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

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
    where IS_NULLABLE = 'YES';

open columnCursor;

fetch next from columnCursor into @tableSchema, @tableName, @columnName;

while @@FETCH_STATUS = 0
    -- 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;

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.


Tags: , , ,

  1. JB’s avatar

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


    1. Cottage’s avatar

      I know its so old and u might not remember this but its not working for me the way u suggested to find empty strings.its giving datatype conversion error. Let me know if u have any other good way. thanks


  2. Karrot’s avatar

    Great script. Thank you!


  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!


  4. L Camp’s avatar

    When I try this I get an error,

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

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

    I could really use this script!



    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…


  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….)


  6. Anders Halden’s avatar

    Thanks a lot, just what I needed!


  7. MERT DOĞAN’s avatar

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


  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?


  9. Cottage’s avatar

    Please let me know how to find empty strings or blanks from all columns.Thanks


  10. belajar mengaji’s avatar

    First off I want to say excellent blog! I had a quick question in which I’d
    like to ask if you do not mind. I was interested to know how
    you center yourself and clear your head before writing.

    I’ve had a difficult time clearing my thoughts
    in getting my thoughts out. I truly do enjoy writing however it just seems like the first 10 to 15 minutes are usually wasted simply
    just trying to figure out how to begin. Any ideas or hints?
    Appreciate it!


  11. Emerson’s avatar

    May I just say what a relief to discover someone
    that genuinely understands what they aare talking about online.

    You definitely nderstand how to bring an issue to light and make it important.
    A lot more people should check this out and understand this side of your story.
    I was surprised you aren’t mote popular sinfe you definitely have the


  12. Aurelia’s avatar

    hello!,I lije your writing so a lot! share we keep up a
    correspondence extra approximately your article on AOL?
    I need an expert on this space to solve my problem.
    Maybe that’s you! Taking a look ahead to see you.


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