Friday, July 5, 2013

Here is a script that generates a script to check each column in a particular table whether they are completely NULL.

 A person would only need to take the UNION ALL off of the ending to run it with no problems.


Declare @NULL_COLUMNS TABLE(
table_name varchar(100),
[schema_name] varchar(130),
column_name varchar(150),
SelectStatement varchar(max)
)

Declare @Table_Name varchar(30)

set @Table_Name = 'TableName'

Insert Into @NULL_COLUMNS
Select
t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name,
'Select ''' + SCHEMA_NAME(schema_id) + '.' + t.name + '.' + c.name + ' - '' + Cast(isnull(AN.AmountNULL,0) as varchar(20)) + ''/'' +  Cast(count(1) as varchar(20)) + '' is NULL '' as [Column], isnull(AN.AmountNULL,0) as AmountNULL, count(1) as Total from '+ SCHEMA_NAME(schema_id) + '.'  + t.name + ' ai left join (Select ''' + c.name + ''' as ColumnName, count(1) as AmountNULL from '+ SCHEMA_NAME(schema_id) + '.' + t.name + ' where ' + c.name + ' is null ) AN on AN.ColumnName = ''' + c.name + ''' group by isnull(AN.AmountNULL,0) UNION ALL '  as SelectStatement
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name not in  ('CreatedBy','CreatedDate','LastModified','LastModifiedBy')
and c.is_identity <> 1
and t.name = @Table_Name
ORDER BY schema_name, table_name;


Select * from @NULL_COLUMNS

No comments:

Post a Comment