February 20, 2006 at 9:56 am
I have a strange request...but I am sure it can be done...
I want the column names in a table where that column has no NULL data in it
Anyone help?
Thanks!
Michael
February 20, 2006 at 11:50 am
Try this...
declare @tablename sysname
--set your table name here
set @tablename = ''
create table #tablecols
(
colname sysname,
colid int
)
declare @colname sysname, @sql nvarchar(4000), @rowcnt int, @colid int
insert into #tablecols
select c.[name], c.[colid]
from sysobjects o
inner join syscolumns c on o.[id] = c.[id]
where o.xtype = 'u'
and o.[name] = @tablename
select top 1 @colid = colid, @colname = colname from #tablecols order by colid
set @rowcnt = @@rowcount
while @rowcnt <> 0
begin
set @sql = 'if exists(select * from ' + @tablename
set @sql = @sql + ' where ' + @colname + ' is null)'
set @sql = @sql + ' delete from #tablecols where colid = ' + convert(varchar, @colid)
exec sp_executesql @sql
select top 1 @colid = colid, @colname = colname from #tablecols where colid > @colid order by colid
set @rowcnt = @@rowcount
end
select * from #tablecols
drop table #tablecols
February 20, 2006 at 1:42 pm
Modify the above select statement to the following for skipping the columns which doesn't allow nulls so that the unnecessary checks can be avoided...
select c.[name], c.[colid]
from sysobjects o
inner join syscolumns c on o.[id] = c.[id]
where o.xtype = 'u'
and o.[name] = @tablename and c.isnullable=1
-Krishnan
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply