InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 10:16:44 GMT20http://www.sqlservercentral.com/Forums/Topic126693-163-1.aspxHi - This really helped me field a query from my boss ultra quick - I appreciate your hard work being shared.Tue, 13 Dec 2011 08:06:39 GMTrussella0-96423http://www.sqlservercentral.com/Forums/Topic126693-163-1.aspx<P>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:</P><P>Use pubs</P><P>drop table pubs..tmp_countgoCreate table pubs..tmp_count(dbnm varchar(50), owner varchar(255), tbl varchar(255), col varchar(255), nulls int, tot int)go</P><P>------------------------------------------------------------------------------------ begin batch----------------------------------------------------------------------------------BEGINdeclare @sql Nvarchar(1000), @minid int, @maxid int,@table_n varchar(255), @col varchar(255), @dbnm varchar(50), @ownernm varchar(50)declare @tmp1 table (idint identity(1,1), tbl varchar(255), col varchar(255), owner varchar(255))</P><P>set @dbnm = db_name()truncate table pubs..tmp_countinsert into @tmp1(tbl,col,owner)select so.name, sc.name, su.name from syscolumns scinner join sysobjects so on so.id = sc.idinner join sysusers su on so.uid = su.uidwhere so.name not like 'ERR_%' and so.type = 'U' andsc.isnullable = 1select @minid = 1, @maxid = max(id) from @tmp1while (@minid &lt;<A href="mailto:=@maxid">=@maxid</A>) 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 +'])'</P><P> exec ( @sql ) set @minid = @minid + 1 endEND------------------------------------------------------------------------------------- end batch-----------------------------------------------------------------------------------</P><P>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</P>Tue, 09 Nov 2004 08:35:00 GMTRichard Nadeauhttp://www.sqlservercentral.com/Forums/Topic126693-163-1.aspxComments posted to this topic are about the content posted at <A HREF=http://www.sqlservercentral.com/columnists/lPeysakhovich/exoticsqlnullcolumnanalysis.asp>http://www.sqlservercentral.com/columnistFri, 16 Jul 2004 09:38:00 GMTLeo Peysakhovich