We can assertain the sparse column identification throught he bewlo query:
SET NOCOUNT ON
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)
SELECT @SQL = COALESCE(@SQL,'') + CAST('INSERT INTO #TMP Select ''' + TABLESCHEMA + '.' + REPLACE(TABLENAME,'''','''''') + '.' + COLUMNNAME + ''' AS Clmn, count(*) NullCount, ''' + DATATYPE + ''', (Select count(*) FROM ' + TABLESCHEMA + '.[' + TABLENAME + ']) AS TableCount FROM ' + TABLESCHEMA + '.[' + TABLENAME + '] WHERE [' + COLUMNNAME + '] IS NULL ;' + CHAR(13) AS VARCHAR(MAX))
FROM INFORMATIONSCHEMA.COLUMNS
JOIN sysobjects B
ON INFORMATIONSCHEMA.COLUMNS.TABLENAME = B.NAME
WHERE XTYPE = 'U'
AND INFORMATIONSCHEMA.COLUMNS.TABLESCHEMA = 'dbo'
AND INFORMATIONSCHEMA.COLUMNS.TABLENAME = 'SmgtTrackingWorkRequest'
EXEC( @SQL)
SELECT A.CLMN,
A.NULLCOUNT,
A.TABLECOUNT,
A.DATATYPE,
(A.NULLCOUNT * 1.0 / A.TABLECOUNT) NULLPERCENT,
ISNULL(B.PRCENT,60) * .01 VALUEPERCENT
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 NULLPERCENT DESC
DROP TABLE #TMP
DROP TABLE #SPARCEPERCENTAGE
commented on Oct 14 2011 4:34AM