Identify all columns containing only a single value (e.g. 0,0.00, Z, Y, N)

Question

I have found methods to identify columns with duplicate values and I can use Select Distinct to find columns with only a single value, but I have a lot of tables with hundreds of columns and sometimes millions of rows. I need to find every column name where that column contains only 0, 0.00, Z, N, or Y. Essentially trying to create a list of useless columns.

How do I iterate select distinct (or something along those lines) across every column, one column at a time, and identify those instances in a table where the column values are the same in every row? Or alternatively, instances where the row count is less than 2.

This seems sort of close but specifying the value isn't going to help: SQL Server SELECT where any column contains 'x'

Group by and Having > 1 sort of works but I would have to run it for each column.

Select Distinct works but I have to run it one column at a time unless there is some better method I am missing.

I got a few of these methods to cooperate but only on numeric columns: Find columns that contain only zeros

This works to identify columns containing only NULL but I can't seem to get it do work for any other value:

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

I have started adapting a bit but am hitting errors about incorrect syntax near from:

DECLARE crs CURSOR LOCAL FAST_FORWARD FOR SELECT name FROM syscolumns WHERE id=OBJECT_ID('TABLENAME')
DECLARE @sql NVARCHAR(MAX)
OPEN crs
DECLARE @name sysname
FETCH NEXT FROM crs INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
Create Table #Temp (ID NVARCHAR, Info INT)
SET @sql = N'select distinct ''' + @name + ''' insert into #Temp (ID) from TABLENAME; SELECT COUNT(*) from #Temp Having COUNT(*)<2 insert into  #Temp (Info);' 
EXEC sp_executesql @sql 
DROP TABLE #Temp
FETCH NEXT FROM crs INTO @name
END
CLOSE crs
DEALLOCATE crs

Answer 1

Assuming this is a one-off, it's probably easier and quicker to construct an ad-hoc query like

SELECT
  SUM(CASE WHEN Col1 NOT IN ('0.00', 'Z', 'N', 'Y') THEN 1 ELSE 0 END) AS Col1,
  SUM(CASE WHEN Col2 NOT IN ('0.00', 'Z', 'N', 'Y') THEN 1 ELSE 0 END) AS Col2,
  SUM(CASE WHEN Col3 NOT IN ('0.00', 'Z', 'N', 'Y') THEN 1 ELSE 0 END) AS Col3
FROM
yourtable

Any column with a total >= 1 has useful data.

Notes

• The above does an implicit group as it's not actually grouping on any fields.
• The above assumes all columns are strings (e.g., varchar/nvarchar). If there is a mix, you'll need to tweak it by column
• NULLs also don't count as valid values (e.g., don't add 1 to total)

To get your column list in SSMS, you can do this by

• scripting the table (right click on table -> script table as -> create table), or
• by just opening the table on the left, a new query window on the right, and drag the word 'columns' to the right hand side

Then just use your favorite text editor or similar to create your SQL using the template above - replacing Col1, Col2 etc with the actual column names. If you have a mix of string and numerical types, the first option can be used to group them together for constructing the 'IN' expressions.

Answer 2

Here's one way you could go about this. For each column of the specified table, it does a count of rows that match your criteria and, if the column's row count matches that of the table's, the column is added to the XML list for review.

NOTE:

Given your number of columns and possible row counts, this process could take a while.

DECLARE @Columns table ( column_name varchar(255), pk INT IDENTITY(1,1) );
DECLARE 
    @TableName varchar(50) = 'tblClients',
    @Criteria varchar(255) = 'IN (''0'', ''0.00'', ''Z'', ''N'', ''Y'')',
    @sql nvarchar(MAX),
    @rows int;

/* Get the current row count for @TableName */

SET @sql = 'SELECT @count = COUNT( DISTINCT [' + @col + '] ) FROM ' + @TableName + ';';
    EXEC sp_executesql @sql, N'@count int OUT', @count = @rows OUT;

/* Create an XML object to store fields that match criteria */
DECLARE @List xml = '<root><columns></columns></root>';

    -- Insert the table name and number of rows processed.
    SET @List.modify( 
      'insert
        <table>
          <name>{sql:variable("@TableName")}</name>
          <rows>{sql:variable("@rows")}</rows>
          <criteria>{sql:variable("@Criteria")}</criteria>
        </table>
      as first
      into (//root)[1]' 
    );

/* Collect a list of column names for the specified table */

INSERT INTO @Columns ( column_name )
SELECT [name] FROM sys.columns WHERE object_id = OBJECT_ID( @TableName ) ORDER BY column_id;

/* For-each column, check if it contains: [ 0 | 0.00 | Z | N | Y ] */

DECLARE @pk int = 1, @col varchar(255), @count int;
WHILE @pk <= ( SELECT MAX ( pk ) FROM @Columns ) 
BEGIN

    -- Current column.
    SELECT @col = column_name FROM @Columns WHERE pk = @pk;
    
    -- Get count of rows for this column with the specified values.
    SET @sql = 'SELECT @count = COUNT( DISTINCT [' + @col + '] ) FROM ' + @TableName + ' WITH (NOLOCK) WHERE CAST( [' + @col + '] AS varchar(MAX) ) ' + @Criteria + ';';
        EXEC sp_executesql @sql, N'@count int OUT', @count = @count OUT;

    -- Insert the column for review if its count matches the table row count.
    IF @count BETWEEN 1 AND 2
        SET @List.modify(
          'insert 
            <column>
              <name>{sql:variable("@col")}</name>
              <count>{sql:variable("@count")}</count>
            </column> 
          into (//root/columns)[1]'
        );

    -- Next column.
    SET @pk = @pk + 1;

END

/* SELECT @List results */
SELECT @List AS List;

In my case, the following XML is returned. These columns match your criteria of ones that should be reviewed.

<root>
  <table>
    <name>tblClients</name>
    <rows>143</rows>
    <criteria>IN ('0', '0.00', 'Z', 'N', 'Y')</criteria>
  </table>
  <columns>
    <column>
      <name>WebPortal</name>
      <count>143</count>
    </column>
    <column>
      <name>EnforceQTY</name>
      <count>143</count>
    </column>
    <column>
      <name>CheckForPrepaid</name>
      <count>143</count>
    </column>
    <column>
      <name>ReportNet</name>
      <count>143</count>
    </column>
    <column>
      <name>ActiveClient</name>
      <count>143</count>
    </column>
  </columns>
</root>