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

[{'answer_id': 63312, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 4, 'selected': False, 'text': '<pre><code>SELECT cols\nFROM table\nWHERE cols IS NULL\n</code></pre>\n'}, {'answer_id': 63374, 'author': 'Charles Graham', 'author_id': 7705, 'author_profile': 'https://Stackoverflow.com/users/7705', 'pm_score': -1, 'selected': False, 'text': "<p>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.</p>\n\n<p>I don't know which DBMS you are using, so I'll put some pseudo-code here.</p>\n\n<pre><code>for each col\nbegin\n  @cmd = 'if not exists (select * from tablename where ' + col + ' is not null begin print ' + col + ' end'\nexec(@cmd)\nend\n</code></pre>\n"}, {'answer_id': 63412, 'author': 'Kevin Fairchild', 'author_id': 3743, 'author_profile': 'https://Stackoverflow.com/users/3743', 'pm_score': 3, 'selected': False, 'text': "<p>Or did you want to just see if a column only has NULL values (and, thus, is probably unused)?</p>\n\n<p>Further clarification of the question might help.</p>\n\n<p><strong>EDIT:</strong>\nOk.. here's some really rough code to get you going...</p>\n\n<pre><code>SET NOCOUNT ON\nDECLARE @TableName Varchar(100)\nSET @TableName='YourTableName'\nCREATE TABLE #NullColumns (ColumnName Varchar(100), OnlyNulls BIT)\nINSERT INTO #NullColumns (ColumnName, OnlyNulls) SELECT c.name, 0 FROM syscolumns c INNER JOIN sysobjects o ON c.id = o.id AND o.name = @TableName AND o.xtype = 'U'\nDECLARE @DynamicSQL AS Nvarchar(2000)\nDECLARE @ColumnName Varchar(100)\nDECLARE @RC INT\n    SELECT TOP 1 @ColumnName = ColumnName FROM #NullColumns WHERE OnlyNulls=0\n    WHILE @@ROWCOUNT &gt; 0\n    BEGIN\n        SET @RC=0\n        SET @DynamicSQL = 'SELECT TOP 1 1 As HasNonNulls FROM ' + @TableName + ' (nolock) WHERE ''' + @ColumnName + ''' IS NOT NULL'\n        EXEC sp_executesql @DynamicSQL\n        set @RC=@@rowcount\n        IF @RC=1\n        BEGIN\n            SET @DynamicSQL = 'UPDATE #NullColumns SET OnlyNulls=1 WHERE ColumnName=''' + @ColumnName + ''''\n            EXEC sp_executesql @DynamicSQL\n        END\n        ELSE\n        BEGIN\n            SET @DynamicSQL = 'DELETE FROM #NullColumns WHERE ColumnName=''' + @ColumnName+ ''''\n            EXEC sp_executesql @DynamicSQL\n        END\n    SELECT TOP 1 @ColumnName = ColumnName FROM #NullColumns WHERE OnlyNulls=0\n    END\n\nSELECT * FROM #NullColumns\n\nDROP TABLE #NullColumns\nSET NOCOUNT OFF\n</code></pre>\n\n<p>Yes, there are easier ways, but I have a meeting to go to right now.  Good luck!</p>\n"}, {'answer_id': 63432, 'author': 'Daniel Papasian', 'author_id': 7548, 'author_profile': 'https://Stackoverflow.com/users/7548', 'pm_score': 0, 'selected': False, 'text': '<p>You\'ll have to loop over the set of columns and check each one.  You should be able to get a list of all columns with a DESCRIBE table command.</p>\n\n<p>Pseudo-code:</p>\n\n<p><pre><code>\nforeach $column ($cols) {\n   query("SELECT count(*) FROM table WHERE $column IS NOT NULL")\n   if($result is zero)  {\n      # $column contains only null values"\n      push @onlyNullColumns, $column;\n   } else {\n      # $column contains non-null values\n   }\n}\nreturn @onlyNullColumns;\n</pre></code></p>\n\n<p>I know this seems a little counterintuitive but SQL does not provide a native method of selecting columns, only rows.</p>\n'}, {'answer_id': 63552, 'author': 'kristof', 'author_id': 3241, 'author_profile': 'https://Stackoverflow.com/users/3241', 'pm_score': 2, 'selected': False, 'text': "<p>You can do: </p>\n\n<pre><code>select \n  count(&lt;columnName&gt;)\nfrom\n  &lt;tableName&gt;\n</code></pre>\n\n<p>If the count returns 0 that means that all rows in that column all NULL (or there is no rows at all in the table)</p>\n\n<p>can be changed to </p>\n\n<pre><code>select \n    case(count(&lt;columnName&gt;)) when 0 then 'Nulls Only' else 'Some Values' end\nfrom \n    &lt;tableName&gt;\n</code></pre>\n\n<p>If you want to automate it you can use system tables to iterate the column names in the table you are interested in</p>\n"}, {'answer_id': 63565, 'author': 'squadette', 'author_id': 7754, 'author_profile': 'https://Stackoverflow.com/users/7754', 'pm_score': 0, 'selected': False, 'text': '<p>I would also recommend to search for fields which all have the same value, not just NULL.</p>\n\n<p>That is, for each column in each table do the query:</p>\n\n<pre><code>SELECT COUNT(DISTINCT field) FROM tableName\n</code></pre>\n\n<p>and concentrate on those which return 1 as a result.</p>\n'}, {'answer_id': 63641, 'author': 'MobyDX', 'author_id': 3923, 'author_profile': 'https://Stackoverflow.com/users/3923', 'pm_score': 3, 'selected': False, 'text': '<p>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.</p>\n\n<pre><code>DECLARE crs CURSOR LOCAL FAST_FORWARD FOR SELECT name FROM syscolumns WHERE id=OBJECT_ID(\'Person\')\nOPEN crs\nDECLARE @name sysname\nFETCH NEXT FROM crs INTO @name\nWHILE @@FETCH_STATUS = 0\nBEGIN\n    EXEC(\'SELECT \'\'\' + @name + \'\'\' WHERE NOT EXISTS (SELECT * FROM Person WHERE \' + @name + \' IS NOT NULL)\')\n    FETCH NEXT FROM crs INTO @name\nEND\nCLOSE crs\nDEALLOCATE crs\n</code></pre>\n'}, {'answer_id': 63772, 'author': 'Charles Graham', 'author_id': 7705, 'author_profile': 'https://Stackoverflow.com/users/7705', 'pm_score': 7, 'selected': True, 'text': "<p>Here is the sql 2005 or later version:  Replace ADDR_Address with your tablename.</p>\n\n<pre><code>declare @col varchar(255), @cmd varchar(max)\n\nDECLARE getinfo cursor for\nSELECT c.name FROM sys.tables t JOIN sys.columns c ON t.Object_ID = c.Object_ID\nWHERE t.Name = 'ADDR_Address'\n\nOPEN getinfo\n\nFETCH NEXT FROM getinfo into @col\n\nWHILE @@FETCH_STATUS = 0\nBEGIN\n    SELECT @cmd = 'IF NOT EXISTS (SELECT top 1 * FROM ADDR_Address WHERE [' + @col + '] IS NOT NULL) BEGIN print ''' + @col + ''' end'\n    EXEC(@cmd)\n\n    FETCH NEXT FROM getinfo into @col\nEND\n\nCLOSE getinfo\nDEALLOCATE getinfo\n</code></pre>\n"}, {'answer_id': 63868, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 1, 'selected': False, 'text': "<p>If you need to list all rows where all the column values are <code>NULL</code>, then i'd use the <code>COLLATE</code> 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 <code>IS NULL</code>, you should get all the rows containing only nulls.</p>\n\n<pre><code>SELECT * FROM MyTable WHERE COLLATE(Col1, Col2, Col3, Col4......) IS NULL\n</code></pre>\n\n<p>You shouldn't really have any tables with ALL the <code>columns</code> null, as this means you don't have a <code>primary key</code> (not allowed to be <code>null</code>). Not having a primary key is something to be avoided; this breaks the first normal form.</p>\n"}, {'answer_id': 16550333, 'author': 'Jasmina Shevchenko', 'author_id': 2370941, 'author_profile': 'https://Stackoverflow.com/users/2370941', 'pm_score': 1, 'selected': False, 'text': "<p>Try this -</p>\n\n<pre><code>DECLARE @table VARCHAR(100) = 'dbo.table'\n\nDECLARE @sql NVARCHAR(MAX) = ''\n\nSELECT @sql = @sql + 'IF NOT EXISTS(SELECT 1 FROM ' + @table + ' WHERE ' + c.name + ' IS NOT NULL) PRINT ''' + c.name + ''''\nFROM sys.objects o\nJOIN sys.columns c ON o.[object_id] = c.[object_id]\nWHERE o.[type] = 'U'\n    AND o.[object_id] = OBJECT_ID(@table)\n    AND c.is_nullable = 1\n\nEXEC(@sql)\n</code></pre>\n"}, {'answer_id': 24685175, 'author': 'user2466387', 'author_id': 2466387, 'author_profile': 'https://Stackoverflow.com/users/2466387', 'pm_score': 3, 'selected': False, 'text': "<p>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.</p>\n\n<p>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.</p>\n\n<pre><code>SET NOCOUNT ON;\n\nDECLARE\n @ColumnName sysname\n,@DataType nvarchar(128)\n,@cmd nvarchar(max)\n,@TableSchema nvarchar(128) = 'dbo'\n,@TableName sysname = 'TableName';\n\nDECLARE getinfo CURSOR FOR\nSELECT\n     c.COLUMN_NAME\n    ,c.DATA_TYPE\nFROM\n    INFORMATION_SCHEMA.COLUMNS AS c\nWHERE\n    c.TABLE_SCHEMA = @TableSchema\n    AND c.TABLE_NAME = @TableName;\n\nOPEN getinfo;\n\nFETCH NEXT FROM getinfo INTO @ColumnName, @DataType;\n\nWHILE @@FETCH_STATUS = 0\nBEGIN\n    SET @cmd = N'IF NOT EXISTS (SELECT * FROM ' + @TableSchema + N'.' + @TableName + N' WHERE [' + @ColumnName + N'] IS NOT NULL) RAISERROR(''' + @ColumnName + N' (' + @DataType + N')'', 0, 0) WITH NOWAIT;';\n    EXECUTE (@cmd);\n\n    FETCH NEXT FROM getinfo INTO @ColumnName, @DataType;\nEND;\n\nCLOSE getinfo;\nDEALLOCATE getinfo;\n</code></pre>\n\n<p></p>\n"}, {'answer_id': 24685590, 'author': 'user3827049', 'author_id': 3827049, 'author_profile': 'https://Stackoverflow.com/users/3827049', 'pm_score': 0, 'selected': False, 'text': "<pre><code>SELECT  t.column_name\nFROM    user_tab_columns t\nWHERE   t.nullable = 'Y' AND t.table_name = 'table name here' AND t.num_distinct = 0;\n</code></pre>\n"}, {'answer_id': 43806546, 'author': 'Sylvain Bruyere', 'author_id': 7969179, 'author_profile': 'https://Stackoverflow.com/users/7969179', 'pm_score': 0, 'selected': False, 'text': "<p>An updated version of 'user2466387' version, with an additional small test which can improve performance, because it's useless to test non nullable columns:</p>\n\n<pre><code>AND IS_NULLABLE = 'YES'\n</code></pre>\n\n<p>The full code:</p>\n\n<pre><code>SET NOCOUNT ON;\n\nDECLARE\n @ColumnName sysname\n,@DataType nvarchar(128)\n,@cmd nvarchar(max)\n,@TableSchema nvarchar(128) = 'dbo'\n,@TableName sysname = 'TableName';\n\nDECLARE getinfo CURSOR FOR\nSELECT\n     c.COLUMN_NAME\n    ,c.DATA_TYPE\nFROM\n    INFORMATION_SCHEMA.COLUMNS AS c\nWHERE\n    c.TABLE_SCHEMA = @TableSchema\n    AND c.TABLE_NAME = @TableName\n    AND IS_NULLABLE = 'YES';\n\nOPEN getinfo;\n\nFETCH NEXT FROM getinfo INTO @ColumnName, @DataType;\n\nWHILE @@FETCH_STATUS = 0\nBEGIN\n    SET @cmd = N'IF NOT EXISTS (SELECT * FROM ' + @TableSchema + N'.' + @TableName + N' WHERE [' + @ColumnName + N'] IS NOT NULL) RAISERROR(''' + @ColumnName + N' (' + @DataType + N')'', 0, 0) WITH NOWAIT;';\n    EXECUTE (@cmd);\n\n    FETCH NEXT FROM getinfo INTO @ColumnName, @DataType;\nEND;\n\nCLOSE getinfo;\nDEALLOCATE getinfo;\n</code></pre>\n"}, {'answer_id': 44392513, 'author': 'user8120267', 'author_id': 8120267, 'author_profile': 'https://Stackoverflow.com/users/8120267', 'pm_score': 1, 'selected': False, 'text': "<p>Not actually sure about 2005, but 2008 ate it:</p>\n\n<pre><code>USE [DATABASE_NAME] -- !\nGO\n\nDECLARE @SQL NVARCHAR(MAX)\nDECLARE @TableName VARCHAR(255)\n\nSET @TableName = 'TABLE_NAME'   -- !\n\nSELECT @SQL = \n(\n    SELECT \n        CHAR(10)\n        +'DELETE FROM ['+t1.TABLE_CATALOG+'].['+t1.TABLE_SCHEMA+'].['+t1.TABLE_NAME+'] WHERE '\n        +(\n            SELECT  \n            CASE t2.ORDINAL_POSITION \n                WHEN (SELECT MIN(t3.ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS t3 WHERE t3.TABLE_NAME=t2.TABLE_NAME) THEN ''\n                ELSE  'AND '\n            END\n            +'['+COLUMN_NAME+'] IS NULL' AS 'data()'\n            FROM INFORMATION_SCHEMA.COLUMNS t2 WHERE t2.TABLE_NAME=t1.TABLE_NAME FOR XML PATH('')\n         )  AS 'data()'\n    FROM INFORMATION_SCHEMA.TABLES t1 WHERE t1.TABLE_NAME = @TableName FOR XML PATH('')\n)\n\nSELECT @SQL -- EXEC(@SQL)\n</code></pre>\n"}, {'answer_id': 57849043, 'author': 'Akila Viduranga Liyanaarachchi', 'author_id': 6885539, 'author_profile': 'https://Stackoverflow.com/users/6885539', 'pm_score': 1, 'selected': False, 'text': "<p>Here I have created a script for any kind of SQL table. please copy this stored procedure and create this on your Environment and run this stored procedure with your Table.</p>\n\n<pre><code>exec [dbo].[SP_RemoveNullValues] 'Your_Table_Name'\n</code></pre>\n\n<p>stored procedure</p>\n\n<pre><code>GO\n/****** Object:  StoredProcedure [dbo].[SP_RemoveNullValues]    Script Date: 09/09/2019 11:26:53 AM ******/\nSET ANSI_NULLS ON\nGO\nSET QUOTED_IDENTIFIER ON\nGO\n-- akila liyanaarachchi\nCreate procedure [dbo].[SP_RemoveNullValues](@PTableName Varchar(50) ) as \nbegin\n\n\nDECLARE Cussor CURSOR FOR \nSELECT COLUMN_NAME,TABLE_NAME,DATA_TYPE\nFROM INFORMATION_SCHEMA.COLUMNS\nWHERE TABLE_NAME = @PTableName  \n\nOPEN Cussor;\n\nDeclare @ColumnName Varchar(50)\nDeclare @TableName  Varchar(50)\nDeclare @DataType Varchar(50)\nDeclare @Flage  int \n\nFETCH NEXT FROM Cussor INTO @ColumnName,@TableName,@DataType\nWHILE @@FETCH_STATUS = 0\nBEGIN\n\nset @Flage=0\n\n\nIf(@DataType in('bigint','numeric','bit','smallint','decimal','smallmoney','int','tinyint','money','float','real'))\nbegin\nset @Flage=1\nend \nIf(@DataType in('date','atetimeoffset','datetime2','smalldatetime','datetime','time'))\nbegin\nset @Flage=2\nend \nIf(@DataType in('char','varchar','text','nchar','nvarchar','ntext'))\nbegin\nset @Flage=3\nend \n\nIf(@DataType in('binary','varbinary'))\nbegin\nset @Flage=4\nend \n\n\n\nDECLARE @SQL VARCHAR(MAX) \n\nif  (@Flage in(1,4))\nbegin \n\nSET @SQL ='  update ['+@TableName+'] set ['+@ColumnName+']=0 where ['+@ColumnName+'] is null'\nend \n\nif  (@Flage =3)\nbegin \n\nSET @SQL ='  update ['+@TableName+'] set ['+@ColumnName+'] = '''' where ['+@ColumnName+'] is null '\nend \n\nif  (@Flage =2)\nbegin \n\nSET @SQL ='  update ['+@TableName+'] set ['+@ColumnName+'] ='+'''1901-01-01 00:00:00.000'''+' where ['+@ColumnName+'] is null '\nend \n\n\nEXEC(@SQL)\n\n\n\nFETCH NEXT FROM Cussor INTO @ColumnName,@TableName,@DataType\nEND\n\nCLOSE Cussor\nDEALLOCATE Cussor\n\nEND\n</code></pre>\n"}]
['https://Stackoverflow.com/questions/63291', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/299/']