Find all fields with null values in table

  • Hi - Does anyone know of an easy (or programmatic) way to determine which fields in a table actually have null values for one or more records?

    I commonly have to integrate new tables into my database from other sources and it is a pain to have to write out the T-SQL to check each field individually for nulls. I was hoping to find a script here for that, but no luck so far. Thanks.

  • here's a procedure that you pass the tablename...it cursors thru every column in the table, and if any column is null, adds it to the results table, and finally returns the results, as well as the diagnostic sql if you need to rerun them individually.

    typical results:

    TBLNAMECOLNAMESQL
    GMACTAACCOMPLTBLKEYSELECT * FROM GMACT WHERE AACCOMPLTBLKEY IS NULL
    GMACTACCNBRTBLKEYSELECT * FROM GMACT WHERE ACCNBRTBLKEY IS NULL
    GMACTACCOMPRPTSELECT * FROM GMACT WHERE ACCOMPRPT IS NULL
    GMACTACCOMPRPTACTSELECT * FROM GMACT WHERE ACCOMPRPTACT IS NULL

    ALTER PROCEDURE UGLYSEARCH

    -- EXEC UGLYSEARCH 'GMACT'

    @TABLENAME        VARCHAR(60)

    AS

    SET NOCOUNT ON

    DECLARE @SQL      VARCHAR(500),

    @COLUMNNAME       VARCHAR(60)

    CREATE TABLE #RESULTS(TBLNAME VARCHAR(60),COLNAME VARCHAR(60),SQL VARCHAR(600))

    SELECT

      SYSOBJECTS.NAME AS TBLNAME,

      SYSCOLUMNS.NAME AS COLNAME,

      TYPE_NAME(SYSCOLUMNS.XTYPE) AS DATATYPE

      INTO #FKFINDER

        FROM SYSOBJECTS

          INNER JOIN SYSCOLUMNS ON SYSOBJECTS.ID=SYSCOLUMNS.ID

        WHERE SYSOBJECTS.XTYPE='U'

        AND SYSOBJECTS.NAME=@TABLENAME

        ORDER BY TBLNAME,COLNAME

    DECLARE C1 CURSOR FOR

    SELECT TBLNAME,COLNAME FROM #FKFINDER ORDER BY TBLNAME,COLNAME

    OPEN C1

    FETCH NEXT FROM C1 INTO @TABLENAME,@COLUMNNAME

    WHILE @@FETCH_STATUS <> -1

        BEGIN

            --SET @SQL = 'SELECT ''' + @TABLENAME + ''' AS TABLENAME,''' + @COLUMNNAME + ''' AS COLUMNNAME,* FROM ' + @TABLENAME + ' WHERE ' +  @COLUMNNAME + ' LIKE ''%' + @SEARCHSTRING + '%'''

            SET @SQL = 'IF EXISTS(SELECT * FROM ' + @TABLENAME + ' WHERE ' +  @COLUMNNAME + ' IS NULL) INSERT INTO #RESULTS(TBLNAME,COLNAME,SQL) VALUES(''' + @TABLENAME + ''',''' +  @COLUMNNAME + ''','' SELECT * FROM  ' + @TABLENAME + '  WHERE ' + @COLUMNNAME + ' IS NULL'') ;'

            PRINT @SQL

            EXEC (@SQL)

    FETCH NEXT FROM C1 INTO @TABLENAME,@COLUMNNAME

        END

    CLOSE C1

    DEALLOCATE C1

    SELECT * FROM #RESULTS

     

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • I needed the same thing a while back.  Try this out.  It's not pretty, but it gets the job done.

    If you are on SQL Server 2005, you could use also use a CTE to simplify this.

     

    -- Find all of the columns in the specified table that contain nulls

    SET

    NOCOUNT ON

    GO

    -- Establish the table name to query

    DECLARE

    @tableName NVARCHAR(100)

    SET

    @tableName = 'my_table'

    DECLARE

    @counter INT

    SET

    @counter = 0

    CREATE

    TABLE #tmp

    (

    ID

    INT IDENTITY(1,1)

    , ColumnName NVARCHAR(100)

    , HasNull BIT DEFAULT 0

    )

    INSERT

    #tmp (ColumnName)

    SELECT

    [name] FROM syscolumns

    WHERE

    [id] = (SELECT [id] FROM sysobjects WHERE xtype = 'U' AND [name] = @tableName)

    DECLARE

    @colName NVARCHAR(100)

    WHILE

    @counter < (SELECT COUNT(*) FROM #tmp)

    BEGIN

    SET @counter = @counter + 1

    DECLARE @sql NVARCHAR(MAX)

    SET @sql = 'SELECT @result = COUNT(*) FROM ' + @tableName + ' WHERE '

    + (SELECT ColumnName FROM #tmp WHERE ID = @counter)

    + ' IS NULL'

    DECLARE @result INT

    EXEC sp_executesql @sql, N'@result INT OUTPUT', @result OUTPUT

    IF @result > 0

    UPDATE #tmp

    SET HasNull = 1

    WHERE ID = @counter

    END

    SELECT

    @tableName [Table], [ColumnName] FROM #tmp

    WHERE

    HasNull = 1

    DROP

    TABLE #tmp

    Tim Mitchell, Microsoft Data Platform MVP
    Data Warehouse and ETL Consultant
    TimMitchell.net | @Tim_Mitchell | Tyleris.com
    ETL Best Practices

  • Or my really quick and dirty one

     

    select name

    into #t

    from syscolumns

    where id = object_id('growthdetails')

    create table #result (

    ColName varchar(30),

    NullCount int)

    declare @qry varchar(500), @columnname varchar(30)

    select top 1 @columnname =  name from #t order by name

    while  (select count(*) from #t) > 0

    begin

     select top 1 @qry =  'insert into #result select "'+ name + '", count(*) from growthdetails where [' + name  + '] is null group by [' + name +']'

     from #t

     exec (@qry)

     delete  from #t where name = @columnname

     select top 1 @columnname =  name from #t order by name

    end

    select * from #result

     

  • more than one way to do it, and lots of quick answers; that's why i love this site.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Me too! Post a thread, go to lunch, come back to multiple answers. Thanks, you guys are great! Now I just have to pick one.....

  • Yes, but mine's the best.  It's in color......

    Tim Mitchell, Microsoft Data Platform MVP
    Data Warehouse and ETL Consultant
    TimMitchell.net | @Tim_Mitchell | Tyleris.com
    ETL Best Practices

  • Here's another one in color.  Why search the table once for every column?

    USE

    Northwind

    DECLARE @schema sysname, @table sysname

    SET @schema = 'dbo'

    SET @table = 'Employees'

    SET NOCOUNT ON

    SELECT cmd FROM (

    SELECT 0 AS ordinal_position, 'SET ANSI_WARNINGS OFF

    SET NOCOUNT ON

    SELECT CAST(''Columns with NULLS in ' + @schema + '.' + @table + '

    ''' AS cmd

    UNION ALL

    SELECT ordinal_position,

    ' + CASE WHEN COUNT(' + QUOTENAME(column_name) + ') < COUNT(*) THEN ''' + column_name + '

    '' ELSE '''' END' AS cmd

    FROM INFORMATION_SCHEMA.columns WHERE table_schema = @schema AND table_name = @table

    AND data_type NOT IN ('text', 'ntext', 'image')

    UNION ALL

    SELECT 99999 AS ordinal_position,

    'AS VARCHAR(1000))

    FROM ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table)

    ) x

    ORDER BY ordinal_position

    Generates a script that produces:

    ----------------------------------------------------------------------------------

    Columns with NULLS in dbo.Employees

    Region

    ReportsTo

  • Thanks, Scott. But there seems to be an error because when I ran your script I got these results:

    cmd

    SET ANSI_WARNINGS OFF SET NOCOUNT ON SELECT CAST('Columns with NULLS in dbo.Employees '

    + CASE WHEN COUNT([EmployeeID]) < COUNT(*) THEN 'EmployeeID ' ELSE '' END

    + CASE WHEN COUNT([LastName]) < COUNT(*) THEN 'LastName ' ELSE '' END

    + CASE WHEN COUNT([FirstName]) < COUNT(*) THEN 'FirstName ' ELSE '' END

    ...

  • Did you switch to text mode before running it?  Grid mode screws up the line breaks.

  • Hmm, I still get the same results when I display the results as text (is that what you mean by text mode?).

  • The script I get look like this:

    SET

    ANSI_WARNINGS OFF

    SET NOCOUNT ON

    SELECT CAST('Columns with NULLS in dbo.Employees

    '

    + CASE WHEN COUNT([EmployeeID]) < COUNT(*) THEN 'EmployeeID

    ' ELSE '' END

    + CASE WHEN COUNT([LastName]) < COUNT(*) THEN 'LastName

    ' ELSE '' END

    ...

    + CASE WHEN COUNT([PhotoPath]) < COUNT(*) THEN 'PhotoPath

    ' ELSE '' END

    AS VARCHAR(1000))

    FROM [dbo].[Employees]

    Your version looks like the line breaks are missing.  Did you copy the script exactly from my post, with all line breaks intact?

    I copied the posted version again, ran it, and ran the resulting script, and I got the results shown above.

  • Sorry, you're right - it does work correctly in text mode. Thanks a lot!

  • Try this (using Pubs as a test...)!

    Use Pubs

    Exec sp_MSForEachTable

    'Set NoCount On;

    Declare @wc VarChar(8000);

    Set @wc=''''

    Select @wc=@wc+'' [''+[Name]+''] is Null or'' from dbo.syscolumns with (nolock) where [id]=object_id(''?'') and IsNullable=1;

    If Len(@wc)>0 Begin

       Set @wc=''where''+Left(@wc,Len(@wc)-3)

       Exec(''If Exists(Select * from ? ''+@wc+'') Print ''''?'''''')

    End'



    PeteK
    I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply