Find candidates for sparse columns in SQL Server

sparseColumns.sql

SET nocount ON;

IF Object_id('tempdb..#SPARCEPERCENTAGE') IS NOT NULL
	BEGIN
		DROP TABLE #sparcepercentage
	END;

IF Object_id('tempdb..#TMP') IS NOT NULL
	BEGIN
		DROP TABLE #tmp
	END;

DECLARE @SQL VARCHAR(MAX);

CREATE TABLE #sparcepercentage
	(
		 datatype VARCHAR(50)
		,prcent INT
	);

INSERT INTO #sparcepercentage
SELECT	'bit'
		 , 98
UNION ALL
SELECT	'tinyint'
			 , 86
UNION ALL
SELECT	'smallint'
			 , 76
UNION ALL
SELECT	'int'
			 , 64
UNION ALL
SELECT	'bigint'
			 , 52
UNION ALL
SELECT	'real'
			 , 64
UNION ALL
SELECT	'float'
			 , 52
UNION ALL
SELECT	'smallmoney'
			 , 64
UNION ALL
SELECT	'money'
			 , 52
UNION ALL
SELECT	'smalldatetime'
			 , 64
UNION ALL
SELECT	'datetime'
			 , 52
UNION ALL
SELECT	'uniqueidentifier'
			 , 43
UNION ALL
SELECT	'date'
			 , 69;

CREATE TABLE #tmp
	(	 clmn		VARCHAR(500)
		,nullcount	INT
		,datatype	VARCHAR(50)
		,tablecount	INT
		,isSparse	BIT
	);

SELECT	@SQL = COALESCE(@SQL, '') +
		CAST('INSERT INTO #TMP SELECT ''' + SCHEMA_NAME(T.[schema_id]) + '.' + REPLACE
		(	T.name, '''', '''''') + '.' + C.name +
			''' AS clmn, COUNT(*) NullCount, ''' + TY.name +
			''', (SELECT COUNT(*) FROM ' + SCHEMA_NAME(T.[schema_id]) + '.[' +
			T.name + ']) AS TableCount, ''' + CAST(C.is_sparse AS CHAR(1)) + ''' FROM ' +
			SCHEMA_NAME(T.[schema_id]) + '.[' + T.name +
			'] WHERE [' + C.name + '] IS NULL ;' + CHAR(13)
		AS VARCHAR(MAX))
FROM	sys.tables AS T
JOIN	sys.columns AS C
	ON T.[object_id] = C.[object_id]
JOIN	sys.types AS TY
	ON	C.user_type_id = TY.user_type_id
WHERE	T.name = 'log'

EXEC( @SQL );

SELECT	 A.clmn
		,A.nullCount
		,A.tableCount
		,A.datatype
		,A.isSparse
		,CAST(( A.nullcount * 1.0 / A.tablecount ) AS DECIMAL(3,2)) AS nullPercent
		,(ISNULL(B.prcent, 60) * .01) AS sparseThreshold
FROM	 #tmp A
LEFT JOIN #sparcepercentage B
	ON A.datatype = B.datatype
WHERE	A.nullcount > 0
AND		(A.nullcount * 1.0 / A.tablecount) >= ISNULL(B.prcent, 60) * .01
ORDER BY clmn ASC;

DROP TABLE #tmp;
DROP TABLE #sparcepercentage;