Columns with Null Values? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Columns with Null Values?

Author

Topic

sqldba20
Posting Yak Master

183 Posts

Posted - 2009-02-03 : 10:40:00

Folks:

I need help with finding columns which has NULL values. Here is my table structure and the code I use.

On a daily basis we load data to this table. At the end we would like to know for that Date (AsDate) which columns had NULL values and display it.

I use the following code (in red) to find the column name but I would also like to know which ID so that it would be easier to sort?

Table Columns:

CREATE TABLE <TABLENAME>
(ID int IDENTITY(1,1) NOT NULL,
AsDate DATETIME,
SecName varchar(25) NULL,
RefiName varchar(10) NULL,
GenId int NULL,
IssName varchar(25) NULL,
OrigMat varchar(50) NULL)

DECLARE @colname varchar(255)
DECLARE @Qry varchar(max)

DECLARE getcolnames cursor for

SELECT c.name FROM sys.tables t
JOIN sys.columns c
ON t.Object_ID = c.Object_ID
WHERE t.Name = 'TABLE NAME'

OPEN getcolnames

FETCH NEXT FROM getcolnames into @colname

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @Qry = 'IF EXISTS (SELECT top 1 * FROM TABLENAME
WHERE [' + @colname + '] IS NULL) BEGIN print ''' + @colname + ''' end'
EXEC(@Qry)

FETCH NEXT FROM getcolnames into @colname
END

CLOSE getcolnames
DEALLOCATE getcolnames

Thanks !

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-02-03 : 11:03:17

Hello;

I'm curious to know why you chose to use cursors and dynamic SQL for this? The table you posted, is that the exact size or is it considerably larger?

If its small there may be other programmatic approaches like using temp tables, case statements, isnull or coalesce.

r&r

sqldba20
Posting Yak Master

183 Posts

Posted - 2009-02-03 : 11:22:34

The table is much larger (around 150 columns) and there is no specific reason for using cursor. Going forward I maybe using while loop because I cannot pass table name as variable in cursor. The same is case for using dynamic sql for passing table name in variables.

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-02-03 : 12:28:34

Ok,

Check out the answers provided in this forum for a similar question..
[url]http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/a5662d85-ee8d-452a-b505-5a0b8a7678f5/[/url]

They have posted code using cursors like yours so you may be able to adapt it. They have also posted code without cursors.

You may also wish to search this forum for responses.

r&r

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources