How To Check MS-SQL Columns of Unknown Name for Values

 Networking, SQL  Add comments
Feb 222011
 

Problem

Imagine you have an MS-SQL table that has LOTS of columns.  Some of the column names are known, but others are dynamically generated, and their names are not known at run time.  Now imagine that you suspect most of those unknown columns are empty.  How do you determine if any rows contain values in the columns, whose names are unknown?

Incremental Solutions

You could always just show all the values for every column of every row:

SELECT * FROM myTable

However, if your table is large, this may return too much data.  So, you could look for unique values, like so:

SELECT DISTINCT * FROM myTable

This works better.  However, if the known columns contain lots of unique data, the above T-SQL command may still return too much data.  Let’s try to focus on returning the unique values of just the unknown columns!

Final Solution

In our problem, all of the unknown columns begin with a known prefix, “UDA_”.  So, we need to get a list of the column names compiled in a comma separated list, suitable for a second SELECT statement.  We can do this, like so:

DECLARE @myColumnNames NVARCHAR(MAX)
 
SELECT @myColumnNames=COALESCE(@myColumnNames + ',', '') + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (TABLE_NAME='myTable') AND (COLUMN_NAME LIKE 'UDA_%')
 
SELECT @myColumnNames

These statements initialize a local variable, @myColumnNames, and then COALESCE, or compile all the column names from the myTable, which begin with the prefix, “UDA_”, into a comma separated list.  The last statement prints the variable value for debugging purposes, which might look like:

Finally, a simple select statement can be used to return the distinct values of these columns from our table.  The only problem is that you cannot substitute a variable directly into a T-SQL statement, so you have to build the necessary statement string and execute it, like so:

EXEC('SELECT DISTINCT ' + @myColumnNames + ' FROM myTable')

Putting It All Together

DECLARE @myColumnNames NVARCHAR(MAX)
 
SELECT @myColumnNames=COALESCE(@myColumnNames + ',', '') + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (TABLE_NAME='myTable') AND (COLUMN_NAME LIKE 'UDA_%')
 
EXEC('SELECT DISTINCT ' + @myColumnNames + ' FROM myTable')

If the above statements yield a single row with nothing but NULLs, then you know those columns are all empty!  Anything else indicates that at least one of the table rows, for at least one of your columns of unknown name, contains a value!

Share