Posted By

ezerick on 03/26/10

Tagged

sql
find

Versions (?)

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

Statistics

Viewed 2444 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

Highlighted TextExpand

-- 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;

URL: http://blog.hoegaerden.be/2009/02/15/script-find-all-empty-columns-in-database/

Report this snippet Tweet