July 16, 2004 at 9:38 am
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnist
November 9, 2004 at 8:35 am
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
December 13, 2011 at 8:06 am
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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply