Script: Find All Empty Columns In Database

Earlier this week a colleague had an interesting question.  He was working on an application that uses a database containing more than 200 tables and wanted to find all columns in the database where this column is null for all records, so in other words all empty columns.  This would give him an idea of possibly unused (i.e. obsolete) columns.

Obviously he did not want to do this manually.

So I wrote him this script:

/*
DESCRIPTION: Returns a list of all columns in current database
             where the column's value is null for all records.
WRITTEN BY:  Valentino Vranken
CREATED:     2009-02-15
VERSION:     1.0

COPIED FROM: http://blog.hoegaerden.be
*/
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;

Yes I know, it uses a cursor and dynamic SQL but hey, it’s not something that will run every minute in a production environment.  And I couldn’t really think of an easy way to do this without these features.

For more info on dynamic SQL I would like to point you to this excellent website by Erland Sommarskog.  I’ve read his articles since several years now and always keep coming back to them to refresh my memory.  The particular article here is called The Curse and Blessings of Dynamic SQL.

And finally here’s a Microsoft Support article that explains how to capture the output of a dynamic query executed by sp_executesql.  According to the article this feature is not documented in the BOL.  I think this is no longer a valid statement, there’s even an example in the BOL page on sp_executesql.

Share

Tags: , , ,

  1. JB’s avatar

    Like it. Worked great. Added AND [' + @columnName + '] ”” to check for empty strings.

    Reply

  2. Karrot’s avatar

    Great script. Thank you!

    Reply

  3. Sarah’s avatar

    What a great solution! I can not wait to try this on a project I am working on. I was dreading going through everything… I think you just saved me! Thank you so much!

    Reply

  4. L Camp’s avatar

    When I try this I get an error,

    ERROR: Must declare the scalar variable “@tableSchema”.
    Error
    Code: 137

    Query = fetch next from columnCursor into @tableSchema,
    @tableName, @columnName

    I could really use this script!

    Thanks!

    Reply

    1. Valentino Vranken’s avatar

      It won’t work if you execute only the FETCH statement, you’re supposed to run the whole script as is…

      Reply

  5. D Hobbs’s avatar

    Works great at finding columns where all records are NULL. Thank you!

    But I have a problem….some of the records were imported from a web application form and instead of being NULL they have some fields recorded as ZERO instead, so I have certain fields with a mix of only NULLS and ZEROS (ie no “real” data). Is it possible to modify this script to find fields with only NULL and 0? (If so, I can start the cleanout process….)

    Reply

  6. Anders Halden’s avatar

    Thanks a lot, just what I needed!

    Reply

  7. MERT DOĞAN’s avatar

    How can i delete all of null columns with this code?

    Reply

  8. Nefeli’s avatar

    Very helpful indeed!
    Although I would like to ask if it is possible to find all NULL fields for a specific table?

    Reply

© 2008-2017 BI: Beer Intelligence? All Rights Reserved