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?
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.
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.
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)