How do I select all the columns in a table that only contain NULL values for all the rows? I'm using MS SQL Server 2005. I'm trying to find out which columns are not used in the table so I can delete them.

This should give you a list of all columns in the table "Person" that has only NULL-values. You will get the results as multiple result-sets, which are either empty or contains the name of a single column. You need to replace "Person" in two places to use it with another table.

DECLARE crs CURSOR LOCAL FAST_FORWARD FOR SELECT name FROM syscolumns WHERE id=OBJECT_ID('Person')
OPEN crs
DECLARE @name sysname
FETCH NEXT FROM crs INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('SELECT ''' + @name + ''' WHERE NOT EXISTS (SELECT * FROM Person WHERE ' + @name + ' IS NOT NULL)')
FETCH NEXT FROM crs INTO @name
END
CLOSE crs
DEALLOCATE crs

Here is an updated version of Bryan's query for 2008 and later. It uses INFORMATION_SCHEMA.COLUMNS, adds variables for the table schema and table name. The column data type was added to the output. Including the column data type helps when looking for a column of a particular data type. I didn't added the column widths or anything.

For output the RAISERROR ... WITH NOWAIT is used so text will display immediately instead of all at once (for the most part) at the end like PRINT does.

If you need to list all rows where all the column values are NULL, then i'd use the COLLATE function. This takes a list of values and returns the first non-null value. If you add all the column names to the list, then use IS NULL, you should get all the rows containing only nulls.

You shouldn't really have any tables with ALL the columns null, as this means you don't have a primary key (not allowed to be null). Not having a primary key is something to be avoided; this breaks the first normal form.

You might need to clarify a bit. What are you really trying to accomplish? If you really want to find out the column names that only contain null values, then you will have to loop through the scheama and do a dynamic query based on that.

I don't know which DBMS you are using, so I'll put some pseudo-code here.

That should work, but can be simplified... instead of COUNT(*) just do a TOP 1 and set a variable to something signifying you got a hit... Don't need to know how many aren't NULL, just that there's at least one that isn't...
–
Kevin FairchildSep 15 '08 at 14:37