determine smallest datatype to hold values in table

set nocount on ;

— This script will look for smallest datatypes . e.g. smallint instead of nvarchar(255)

declare @sql as varchar(max) =”
,@data_type_id int
,@col_name varchar(max)
, @data_type as varchar(255)
, @col_str as varchar(max) =”

— Step 1 put column names and datatype into #cols table
IF OBJECT_ID(‘tempdb..#cols’) IS NOT NULL
DROP TABLE #cols

select column_id, c.name col_name , c.system_type_id, c.max_length, convert(varchar(255), ”) data_type, convert(varchar(max), ”) check_sql
into #cols
from sys.columns c
where objecT_id = object_id(‘[STG].[KCVS]’)

–select * from #cols
–select * from sys.types

— Step 2 for each col determin smallest type

declare c cursor for
select col_name, dbo.udf_determine_min_datatype(col_name, ‘stg.KCVS’) sql
from #cols
–where column_id >= 1 and column_id < 50

open c
FETCH NEXT FROM c
INTO @col_name , @sql

WHILE @@FETCH_STATUS = 0
BEGIN
IF OBJECT_ID(‘tempdb..#T1’) IS NOT NULL
DROP TABLE #T1

create TABLE #T1 ( value varchar(255) , data_type varchar(100), cnt int )

insert into #T1
execute (@sql)

— next determine datatype if there are > 1
;
with q as(
select
case when value = ‘#NULL!’ then 9999 — ignore
when data_type like ‘smallint%’ then 100 else 10 end seq_nr — order datatypes varchar over smallint
, *
from #T1
)
select @data_type = data_type from q where seq_nr = (select min(seq_nr) from q )
update #cols set data_type = @data_type , check_sql= @sql where col_name =@col_name

FETCH NEXT FROM c
INTO @col_name , @sql
END

CLOSE c
DEALLOCATE c

select * from #cols

select @col_str += col_name + ‘ ‘ + data_type + ‘ NULL,

from #cols

print @col_str
/*

alter function dbo.udf_determine_min_datatype(
@col_name varchar(255),
@tbl_name varchar(255)
)
RETURNS varchar(max)
as
begin
declare @res as int
, @sql as varchar(max) =”

set @sql = ‘
select ‘+@col_name+ ‘,
case when isnumeric(‘+ @col_name + ‘)=1 then ”smallint”
when ‘+@col_name + ‘ is null then ”smallint”
else ”varchar(” + convert(varchar(10),(select max(len(‘+@col_name+ ‘)) from ‘+@tbl_name + ‘) ) +”)” end dt
, count(*) cnt
from ‘+ @tbl_name + ‘ group by ‘+ @col_name

return @sql

end
*/
–select S001aQ1,
— case when isnumeric(S001aQ1)=1 then ‘smallint’
— when S001aQ1 is null then ‘smallint’
— else ‘varchar(‘ + convert(varchar(10),(select max(len(S001aQ1)) from stg.KCVS) ) +’)’ end dt
— , count(*) cnt
— from stg.KCVS group by S001aQ1