SQL Server SQL script to find sparse column candidates

FindSparseColumnCandidates.sql

USE TestDB
GO

DECLARE @DatabaseName VARCHAR(100)
DECLARE @SchemaName VARCHAR(100)
DECLARE @TableName VARCHAR(100)
DECLARE @ColumnName VARCHAR(100)
DECLARE @FullyQualifiedTableName VARCHAR(500)

--Create Temp Table to Save Results
IF OBJECT_ID('tempdb..#Results') IS NOT NULL
    DROP TABLE #Results

CREATE TABLE #Results (
    DatabaseName VARCHAR(100)
    ,SchemaName VARCHAR(100)
    ,TableName VARCHAR(100)
    ,ColumnName VARCHAR(100)
    ,TotalTableRowCount INT
    ,NullRecordCount INT
    )

DECLARE Cur CURSOR
FOR
SELECT C.Table_CataLog
    ,C.Table_Schema
    ,C.Table_Name
    ,C.Column_Name
    ,'[' + C.Table_CataLog + ']' + '.[' + C.Table_Schema + '].' 
    + '[' + C.Table_Name + ']' AS FullQualifiedTableName
FROM information_schema.Columns C
INNER JOIN information_Schema.Tables T ON C.Table_Name = T.Table_Name
    AND T.Table_Type = 'BASE TABLE'
    and C.IS_Nullable='YES'


OPEN Cur

FETCH NEXT
FROM Cur
INTO @DatabaseName
    ,@SchemaName
    ,@TableName
    ,@ColumnName
    ,@FullyQualifiedTableName

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @SQL VARCHAR(MAX) = NULL

    SET @SQL = ' Select ''' + @DatabaseName + ''' AS DatabaseName, ''' 
    + @SchemaName + ''' AS TableName,
      ''' + @TableName + ''' AS SchemaName,
      ''' + @ColumnName + ''' AS ColumnName,
      (Select count(*) from ' + @FullyQualifiedTableName + ' with (nolock)) 
      AS TotalTableRowCount,
      count(*) as NullRecordCount from ' + @FullyQualifiedTableName 
      + 'with (nolock) Where ' + @ColumnName + ' IS NULL' 

    --Print @SQL
    INSERT INTO #Results
    EXEC (@SQL)

    FETCH NEXT
    FROM Cur
    INTO @DatabaseName
        ,@SchemaName
        ,@TableName
        ,@ColumnName
        ,@FullyQualifiedTableName
END

CLOSE Cur

DEALLOCATE Cur


SELECT *,
Cast((NullRecordCount/Cast(TotalTableRowCount as Numeric(13,2)))*100 AS Numeric(13,8))
 AS NullPercentColumnValues
FROM #Results
where NullRecordCount <>0
order by Cast((NullRecordCount/Cast(TotalTableRowCount as Numeric(13,2)))*100 AS Numeric(13,8)) desc
drop table #Results

-- original source - https://www.techbrothersit.com/2016/03/how-to-find-percentage-of-null-values.html