column names in a table where that column has no NULL data in it

  • 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

  • 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

  • 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