Display NULL Columns
-
Hi all,
Simple question.
Is there a way to query a table and return all the column names of columns which have only NULL values in them.
Cheers.
-
Hi, I wrote this stored procedure and test script. The stored procedure installs into the master database, so you can use it in every database. Hope this will help you
use master
go
if object_id('dbo.sp__ListUnusedColumns') is not null drop proc dbo.sp__ListUnusedColumns
go
create proc dbo.sp__ListUnusedColumns ( @tblname sysname)
as
begin
declare @sql varchar(4000), @colname sysname, @tmptable sysname
set @tmptable = '##colnames_' + convert(varchar(12),@@spid)
if object_id('tempdb..' + @tmptable) is not null exec('drop table ' + @tmptable)
set @sql = 'create table ' + @tmptable + ' ( name sysname ) '
exec (@SQL)
declare cColumns cursor LOCAL for
select name
from dbo.syscolumns
where id = object_id(@tblname)
for read only
open cColumns
goto nextcColumns
while @@FETCH_STATUS = 0
begin
set @sql = 'if not exists ( select 1 from ' + @tblname + ' where ' + @colname + ' is not null ) insert ' + @tmptable + ' values (''' + @colname + ''')'
-- print @sql
exec (@SQL)
nextcColumns: fetch cColumns into @colname
end
close cColumns
deallocate cColumns
exec ('select * from ' + @tmptable )
exec('drop table ' + @tmptable)
end
go
-- TEST SCRIPT --
use tempdb
go
if object_id('dbo.nullvalues') is not null drop table dbo.nullvalues
go
create table dbo.nullvalues
(
a1 int null,
a2 int null,
a3 int null,
a4 int null,
a5 int null
)
go
insert dbo.nullvalues (a1) values (1)
insert dbo.nullvalues (a1) values (2)
insert dbo.nullvalues (a1) values (3)
insert dbo.nullvalues (a1) values (4)
insert dbo.nullvalues (a1,a3) values (5,1)
go
exec sp__ListUnusedColumns 'dbo.nullvalues'
go
use master
go
exec tempdb..sp__ListUnusedColumns 'dbo.nullvalues'
go
-
Hi, sorry for late reply.
Thanks very much for you input, I will definately try you SP.
Again, thankyou.
================= UPDATE ==========================
I ran the code and it worked like a dream.
Thankyou yet again for all your help.
Cheers
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply