Determining what columns are all Null

Does anyone have a suggestion for how to efficiently determine what columns in a table are NULL for all rows? - Shean

Hi,

I wrote a procedure for that:

ALTER PROCEDURE spDetermineNullColumns

(

@.TableName SYSNAME

)

AS

BEGIN

DECLARE @.ROWCOUNT INT

DECLARE @.I INT

DECLARE @.HIT INT

DECLARE @.sql NVARCHAR(2000)

SELECT IDENTITY(INT,1,1) AS IDCol,COLUMN_NAME

INTO #NullableColumns

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = @.TableName AND IS_NULLABLE = 'YES'

SET @.ROWCOUNT = @.@.ROWCOUNT

SET @.I = 1

WHILE @.I <= @.ROWCOUNT

BEGIN

SET @.Hit = 0

SELECT @.sql = N'SELECT @.Hit = 1 '+ ' FROM ' + @.TableNAME +

' WHERE ' + COLUMN_NAME + ' IS NOT NULL'

FROM #NullableColumns WHERE IDCol = @.I

PRINT @.SQL

EXEC sp_executesql

@.query = @.sql,

@.params = N'@.Hit INT OUTPUT',

@.Hit = @.Hit OUTPUT

/* This here can be customized as you want to

IF @.Hit = 0

SELECT COLUMN_NAME + ' is completly NULL.'

FROM #NullableColumns WHERE IDCol = @.I

SET @.I = @.I +1

*/

END

END

GO

spDetermineNullColumns 'Categorie'

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||DId that help Sean ?|||

You could do something like below:

select min(case when col1 is not null then 0 else 1 end) as col1_is_all_null

, min(case when col2 is not null then 0 else 1 end) as col2_is_all_null

...

from tbl