tags:

views:

60

answers:

3

How can I check if any column in a given table only have null or empty string values? Can I in some way extend this for every table in the database?

+1  A: 

You can't normally put a condition on a query for all columns in a table. You have to pick the columns you want. To get around this you need dynamic sql and the information_schema views.

Joel Coehoorn
I realize I have to use dynamic sql. This is for maintainence
picknick
+2  A: 

Here is a stored proc for finding an arbitrary value in the database. It's a fairly small modification to make it search for empty columns.

The procedure generates a list of all the tables and all the columns in the database, and creates a temporary table for storing the results. Then it generates a dynamic SQL and uses the INSERT INTO ... EXEC to fill the result table.

SWeko
+2  A: 

Here's a runnable example off the StackOverflow database:

-- Look for NULLs


DECLARE @sql AS varchar(max)

SELECT @sql = COALESCE(@sql + ' UNION ALL ', '') + sql
FROM (
SELECT 'SELECT ''' + c.TABLE_NAME + '.' + c.COLUMN_NAME + ''' AS COLUMN_NAME, COUNT(NULLIF(' + QUOTENAME(c.COLUMN_NAME) + ', '''')) AS NON_NULL_COUNT, COUNT(*) AS TOTAL_COUNT FROM ' + QUOTENAME(c.TABLE_CATALOG) + '.' + QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME) AS sql
FROM INFORMATION_SCHEMA.COLUMNS AS c
INNER JOIN INFORMATION_SCHEMA.TABLES AS t
ON t.TABLE_CATALOG = c.TABLE_CATALOG
AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
AND t.TABLE_NAME = c.TABLE_NAME
WHERE c.DATA_TYPE IN ('nvarchar', 'varchar')
UNION ALL
SELECT 'SELECT ''' + c.TABLE_NAME + '.' + c.COLUMN_NAME + ''' AS COLUMN_NAME, COUNT(' + QUOTENAME(c.COLUMN_NAME) + ') AS NON_NULL_COUNT, COUNT(*) AS TOTAL_COUNT FROM ' + QUOTENAME(c.TABLE_CATALOG) + '.' + QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME) AS sql
FROM INFORMATION_SCHEMA.COLUMNS AS c
INNER JOIN INFORMATION_SCHEMA.TABLES AS t
ON t.TABLE_CATALOG = c.TABLE_CATALOG
AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
AND t.TABLE_NAME = c.TABLE_NAME
WHERE c.DATA_TYPE NOT IN ('nvarchar', 'varchar')
AND c.IS_NULLABLE = 'YES'
) AS checks

SET @sql = 'SELECT * FROM (' + @sql + ') AS checks WHERE NON_NULL_COUNT = 0'

EXEC (@sql)

A few things to note:

There are two columns it finds which are completely NULL/blank:

Posts.OwnerDisplayName, Bdges.CreationDate

It converts '' to NULL for nvarchar and varchar columns (if you have char or nchar columns, you would have to change this)

Cade Roux