Microsoft SQL Information Schema with counts

--Microsoft SQL Information Schema with counts

How to count the number of records in your column fields and display the information schema table and column names along side the counts by creating this dynamic sql query that prepares the sql code for you.  So all you do is copy the sql code from these results and run it.

select @@ROWCOUNT, concat('select ''',table_name,''' as [table_names], ''',column_name,''' as [column_names],','(select count(*) from (select [',information_schema.columns.column_name,'] from [',information_schema.columns.table_name,'] where [',information_schema.columns.column_name,'] is not null) as column_counts) column_counts,
(select count(*) from (select [',information_schema.columns.column_name,'] from [',information_schema.columns.table_name,']) as table_counts) table_counts,',
'iif((select count(*) from (select [',information_schema.columns.column_name,'] from [',information_schema.columns.table_name,']) as table_counts)=0,0,',
'(cast((select count(*) from (select [',information_schema.columns.column_name,'] from [',information_schema.columns.table_name,'] where [',information_schema.columns.column_name,'] is not null) as column_counts) as numeric)/
cast((select count(*) from (select [',information_schema.columns.column_name,'] from [',information_schema.columns.table_name,']) as table_counts) as numeric))) as fxpercentage

union all')
from INFORMATION_SCHEMA.COLUMNS;


Code generated:








Result of running code for one of the records.  you can run them together using the union all statement.  or leave union all statement out to run it for all of them.  but it may take a while: