Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Exotic SQL: NULL Column Analysis Expand / Collapse
Author
Message
Posted Friday, July 16, 2004 9:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 7:25 AM
Points: 138, Visits: 272
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnist


Post #126693
Posted Tuesday, November 9, 2004 8:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 30, 2009 11:13 AM
Points: 1, Visits: 6

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

Post #145433
Posted Tuesday, December 13, 2011 8:06 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 14, 2014 7:48 AM
Points: 70, Visits: 223
Hi - This really helped me field a query from my boss ultra quick - I appreciate your hard work being shared.


Post #1220918
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse