This script can be run against any database.
This script can be run against any database.
SET NOCOUNT ON GO DROP TABLE #tblHold GO DECLARE @tblA TABLE (RowId INT IDENTITY(1,1),SchemaName VARCHAR(65),TableName VARCHAR(65),TotalRows BIGINT) INSERT INTO @tblA SELECT S.[name] AS SchemaName, ST.[name] AS TableName, SUM(p.[rows]) AS TotalRows FROM [sys].[tables] AS ST WITH (NOLOCK) INNER JOIN [sys].[schemas] AS S WITH (NOLOCK) ON ST.[schema_id] = S.[schema_id] INNER JOIN [sys].[partitions] AS P WITH (NOLOCK) ON P.[object_id] = ST.[object_id] WHERE P.[index_id] IN (0,1) GROUP BY S.[name], ST.[name] DECLARE @tblB TABLE (RowId INT IDENTITY(1,1),SchemaName VARCHAR(65),TableName VARCHAR(65),ColumnName VARCHAR(65), ExecSQL VARCHAR(2000),NullRows BIGINT) INSERT INTO @tblB SELECT S.[name] AS SchemaName, ST.[name] AS TableName, SC.[name] AS ColumnName, 'SELECT COUNT(1) FROM ['+DB_NAME()+'].['+S.[name]+'].['+ST.[name]+'] WITH (NOLOCK) WHERE ['+SC.[name]+'] IS NULL' AS ExecSQL, 0 AS NullRows FROM [sys].[tables] AS ST WITH (NOLOCK) INNER JOIN [sys].[schemas] AS S WITH (NOLOCK) ON ST.[schema_id] = S.[schema_id] INNER JOIN [sys].[columns] AS SC WITH (NOLOCK) ON ST.[object_id] = SC.[object_id] WHERE 1=1 AND SC.[is_nullable] = 1 ORDER BY S.[name], ST.[name], SC.[column_id] DECLARE @MinId INT DECLARE @MaxId INT DECLARE @ExecSQL VARCHAR(2000) DECLARE @tblE TABLE(MyRows BIGINT) SELECT @MinId=MIN(RowId),@MaxId=MAX(RowId) FROM @tblB WHILE(@MinId<=@MaxId) BEGIN DELETE @tblE SELECT @ExecSQL=ExecSQL FROM @tblB WHERE RowId = @MinId INSERT INTO @tblE EXEC(@ExecSQL) UPDATE @tblB SET NullRows=(SELECT MyRows FROM @tblE) WHERE RowId = @MinId SELECT @MinId=@MinId+1 END SELECT B.RowId, A.SchemaName, A.TableName, B.ColumnName, B.NullRows, A.TotalRows, CAST(CAST(CAST(NullRows AS VARCHAR(10))+'.00' AS DEC(15,2))/CAST(CAST(TotalRows AS VARCHAR(10))+'.00' AS DEC(15,2))*100 AS DEC(5,2)) AS PercentageNull INTO #tblHold FROM @tblA AS A INNER JOIN @tblB AS B ON A.SchemaName = B.SchemaName AND A.TableName = B.TableName WHERE NullRows > 0 AND NullRows <> TotalRows GO SELECT * FROM #tblHold ORDER BY PercentageNull DESC