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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy