Posted By

ezerick on 03/26/10

Tagged

sql
find

Versions (?)

Last Edited at 03/26/10 11:32am

Statistics

Viewed 2685 times

Favorited by 2 user(s)

Related snippets

TSQL - Find All Empty Columns in a Database

/ Published in: SQL

I found this and simply want to store it. It was done by Valentino Vranken

Expand | Embed | Plain Text

Copy this code and paste it in your HTML

-- Returns a list of all columns in current database
-- where the column's value is null for all records.
DECLARE @tempTable TABLE
(
    TableSchema nvarchar(256),
    TableName nvarchar(256),
    ColumnName sysname,
    NotNullCnt BIGINT
);
 
DECLARE @SQL nvarchar(4000);
DECLARE @tableSchema nvarchar(256);
DECLARE @tableName nvarchar(256);
DECLARE @columnName sysname;
DECLARE @cnt BIGINT;
 
DECLARE columnCursor cursor FOR
    SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
    WHERE IS_NULLABLE = 'YES';
 
OPEN columnCursor;
 
fetch NEXT FROM columnCursor INTO @tableSchema, @tableName, @columnName;
 
while @@FETCH_STATUS = 0
BEGIN
    -- use dynamic sql to get count of records where column is not null
    SET @SQL = 'select @cnt = COUNT(*) from [' + @tableSchema + '].[' + @tableName +
        '] where [' + @columnName + '] is not null';
    -- print @sql; --uncomment for debugging
    EXEC sp_executesql @SQL, N'@cnt bigint output', @cnt = @cnt output;
 
    INSERT INTO @tempTable SELECT @tableSchema, @tableName, @columnName, @cnt;
 
    fetch NEXT FROM columnCursor INTO @tableSchema, @tableName, @columnName;
END
 
close columnCursor;
deallocate columnCursor;
 
SELECT * FROM @tempTable WHERE NotNullCnt = 0;