sql - INFORMATION_SCHEMA.columns won't list two columns -

sql - INFORMATION_SCHEMA.columns won't list two columns -

i have next code supposed display column name, info type, size, # of filled columns , total rows.

the problem running using information_schema.columns can display either data_type or character_maximum_length. if seek select both errors:

an look of non-boolean type specified in context status expected, near 'si'.

the code statement is:

declare @tablename varchar(512) = 'state'; declare @sql varchar(1024); sqltext ( select row_number() on (order c.name) rownum, 'select ''' + c.name + ''', max(b.data_type) ''data type'', sum(case when ' + c.name + ' null 0 else 1 end) ''filled values'', count(*) ''total records'' information_schema.columns b bring together ' + @tablename + ' on b.column_name = ''' + c.name +'''' sqlrow sys.tables t inner bring together sys.columns c on c.object_id = t.object_id bring together sys.types on c.user_type_id = a.user_type_id t.name = @tablename)

any ideas on how both display?

declare @tablename varchar(512) = 'settings'; declare @sql varchar(1024); sqltext ( select row_number() on (order c.name) rownum, 'select ''' + c.name + ''', max(b.data_type) ''data type'', max(b.character_maximum_length) ''character_maximum_length'', sum(case when ' + c.name + ' null 0 else 1 end) ''filled values'', count(*) ''total records'' information_schema.columns b bring together ' + @tablename + ' on b.column_name = ''' + c.name +'''' sqlrow sys.tables t inner bring together sys.columns c on c.object_id = t.object_id bring together sys.types on c.user_type_id = a.user_type_id t.name = @tablename) select * sqltext

this works me, tbh though i'd add together improve thought of size column uses.

sum(case when ' + c.name + ' null 0 else datalength(' + c.name + ') end) ''filled values'',

sql sql-server