SQL Server Audit Table Total Count Total Nulls Len of Data

Hello engineers, are you guys working on SQL Server. Are you guys working on TSQL. If yes i have written a TSQL program which right example of

• Execute dynamic SQL
• In Parameters/variables
• Out parameters/variables
• IN/Out in dynamic SQL
• Cursor in TSQL
• Audit of all tables data
• How many records in table
• How many distinct records in each field
• How many not null records
• Maximum length of data in each field

create table all_counts (id int identity , table_name  nvarchar(200), column_name  nvarchar(200), data_type nvarchar(200) , total_count int, distinct_count int , notnull_count  int, data_len int)

Declare 

@table_name varchar(50),

@column_name nvarchar(100),

@data_Type nvarchar(100),

@total_count integer,

@distinct_count integer,

@notnull_count integer,

@data_len integer,

@query_str nvarchar(4000),

@query_str2 nvarchar(4000) ,

@query_str3 nvarchar(4000) ,

@query_str4 nvarchar(4000) ;

declare cur_col Cursor for 

select  

t.TABLE_NAME

,column_name

,data_type 

from 

INFORMATION_SCHEMA.COLUMNS c 

join INFORMATION_SCHEMA.tables t on c.TABLE_NAME = t.TABLE_NAME 

where 

t.TABLE_TYPE ='BASE TABLE' 

open cur_col

fetch next from cur_col into @table_name,@column_name,@data_Type

while @@FETCH_STATUS=0

begin

Set @query_str='Select @total_count= count(@column_name1) from '+ @table_name;

exec sp_executesql @query_str, N'@column_name1 nvarchar(500),@total_count int output' ,@column_name1=@column_name,@total_count=@total_count output

Set @query_str2='Select  @distinct_count= count( distinct ' + @column_name + ') from '+ @table_name+ ' where ' + @column_name + ' is not null';

exec sp_executesql @query_str2, N'@distinct_count int output' ,@distinct_count=@distinct_count output

Set @query_str3='Select  @notnull_count= count(  ' + @column_name + ') from '+ @table_name+ ' where ' + @column_name + ' is not null';

exec sp_executesql @query_str3, N'@notnull_count int output' ,@notnull_count=@notnull_count output

Set @query_str4='Select  @data_len= len(max(  ' + @column_name + ')) from '+ @table_name+ ' where ' + @column_name + ' is not null';

exec sp_executesql @query_str4, N'@data_len int output' ,@data_len=@data_len output

insert into all_counts select @table_name, @column_name, @data_Type, @total_count, @distinct_count, @notnull_count, @data_len

fetch next from cur_col into @table_name,@column_name,@data_Type 

end

close cur_col

deallocate cur_col

I hope this program is helping many engineers in different ways. Rest of usage is depend upon the thinking and capability of an engineer. He/she can modify this program to any extent since this is generic TSQL.

If any faced error while executing above TSQL cause of Table Name or Column Name have special character which was not supported by Dynamic SQL.

Error :

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near 'tablename'

Solution:

We need to update table_name and column name variables from Cursor as below

['+ @table_name +']';