Percentage of NULL Column Rows

,

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

Rate

2 (4)

Share

Share

Rate

2 (4)