Exotic SQL: NULL Column Analysis

  • Leo Peysakhovich

    Hall of Fame

    Points: 3880

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnist

  • Richard Nadeau

    Grasshopper

    Points: 13

    Great article. I found that blew up when the table owner was not the dbo so I added a little bit of code and it worked beautifully. Thanks for sharing. Here is the modified version:

    Use pubs

    drop table pubs..tmp_count

    go

    Create table pubs..tmp_count

    (dbnm varchar(50), owner varchar(255), tbl varchar(255), col varchar(255), nulls int, tot int)

    go

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

    -- begin batch

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

    BEGIN

    declare @sql Nvarchar(1000), @minid int, @maxid int,

    @table_n varchar(255), @col varchar(255), @dbnm varchar(50), @ownernm varchar(50)

    declare @tmp1 table (id

    int identity(1,1), tbl varchar(255), col varchar(255), owner varchar(255))

    set @dbnm = db_name()

    truncate table pubs..tmp_count

    insert into @tmp1(tbl,col,owner)

    select so.name, sc.name, su.name from syscolumns sc

    inner join sysobjects so on so.id = sc.id

    inner join sysusers su on so.uid = su.uid

    where so.name not like 'ERR_%' and so.type = 'U' and

    sc.isnullable = 1

    select @minid = 1, @maxid = max(id) from @tmp1

    while (@minid <=@maxid)

     begin

     select @table_n = tbl,@col = col,@ownernm = owner from @tmp1

     where id = @minid

     select @sql = ' insert into pubs..tmp_count(dbnm, owner, tbl, col , nulls , tot)'

     select @sql = @sql + ' select ''' + @dbnm + ''', ''' + @ownernm + ''', ''' + @table_n + ''', ''' +

      @col + ''', (select count(*) from [' + @ownernm + '].[' + @table_n + '] where ['+ @col + '] is null) '

     select @sql = @sql + ' , (select count(*) from [' + @ownernm + '].[' + @table_n +'])'

     exec ( @sql )

     set @minid = @minid + 1

     end

    END

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

    -- end batch

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

    select left(dbnm,10) as dbnm, left(owner,20) as owner, left(tbl,20) as tbl,

     left(col,20) as col,tot, nulls, tot - nulls as NotNulls

     from pubs..tmp_count order by NotNulls

  • russella0-96423

    SSC-Addicted

    Points: 448

    Hi - This really helped me field a query from my boss ultra quick - I appreciate your hard work being shared.

Viewing 3 posts - 1 through 3 (of 3 total)

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