CODEWITHSUNDEEP
sqlt-sql
picknick
picknick

Reputation: 4007

check for empty columns

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?

Upvotes: 0

Views: 480

Answers (3)

Cade Roux
Cade Roux

Reputation: 89661

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)

Upvotes: 2

SWeko
SWeko

Reputation: 30882

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.

Upvotes: 2

Joel Coehoorn
Joel Coehoorn

Reputation: 415630

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.

Upvotes: 1

Related Questions