April 17, 2003 at 8:43 pm
I know there is a simple solution to this but I am wrecking my brain trying to find it.
I am trying to build an all at once query to list all table and columns names in a given database where the column have only null values.
Thanks for any help.
April 18, 2003 at 9:23 am
You could build some dynamic SQL like this. Run this code in the database you are looking for null columns. Be aware this might run awhile, depending on the size of each table.
set nocount on
declare @tc table(t varchar(128), c varchar(128))
declare @tnc varchar(300)
declare @old_tnc varchar(300)
declare @t varchar(128)
declare @c varchar(128)
declare @cmd varchar(1000)
print 'Columns with null values in database'
print '------------------------------------'
set @old_tnc = char(1)
insert into @tc select table_name, column_name from information_schema.columns
select top 1 @t=t, @c=c from @tc order by t, c
set @tnc = rtrim(@t) + '.' + rtrim(@c)
while @tnc > @old_tnc
begin
set @old_tnc = @tnc
set @cmd = 'declare @cnt int' + char(13) +
'select @cnt=count(*) from ' + rtrim(@t) + ' where ' + rtrim(@c) + ' is not null' + char(13) +
'if @cnt = 0 print ''' + rtrim(@t) + '.' + rtrim(@c) + ''''
exec (@cmd)
select top 1 @t=t, @c=c from @tc where rtrim(t) + '.' + rtrim(c) > @old_tnc order by t, c
set @tnc = rtrim(@t) + '.' + rtrim(@c)
end
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
April 18, 2003 at 2:19 pm
That did the trick pretty much, (It grabbed views as well).
Thanks again.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply