Finding empty columns in SQL Server tables
11 April 2023
Sometimes you have database tables with columns that have no values.
In this specific case, we are exposing the tables to our users as views. That gives us the option to exclude the columns with no values or interest for the users.
But it can be a time-consuming task to check every row in every column in every table for empty ones.
This task can be performed by running this script that I have assembled. Simply replace the value "CUSTOMER" in the first row of the script with the name of the table you want to check.
Here is an example table "CUSTOMER" with 25 rows of data. Notice that the column "GRADE" only contains NULL values and the column "PHONE_NO" has empty values (""). My script will cover both cases.
| CUST_CODE | CUST_NAME | CUST_CITY | WORKING_AREA | CUST_COUNTRY | GRADE | OPENING_AMT | RECEIVE_AMT | PAYMENT_AMT | OUTSTANDING_AMT | PHONE_NO | AGENT_CODE |
|---|---|---|---|---|---|---|---|---|---|---|---|
| C00001 | Micheal | New York | New York | USA | NULL | 3000.00 | 5000.00 | 2000.00 | 6000.00 | A008 | |
| C00002 | Bolt | New York | New York | USA | NULL | 5000.00 | 7000.00 | 9000.00 | 3000.00 | A008 | |
| C00003 | Martin | Torento | Torento | Canada | NULL | 8000.00 | 7000.00 | 7000.00 | 8000.00 | A004 | |
| C00004 | Winston | Brisban | Brisban | Australia | NULL | 5000.00 | 8000.00 | 7000.00 | 6000.00 | A005 | |
| C00005 | Sasikant | Mumbai | Mumbai | India | NULL | 7000.00 | 11000.00 | 7000.00 | 11000.00 | A002 | |
| C00006 | Shilton | Torento | Torento | Canada | NULL | 10000.00 | 7000.00 | 6000.00 | 11000.00 | A004 | |
| C00007 | Ramanathan | Chennai | Chennai | India | NULL | 7000.00 | 11000.00 | 9000.00 | 9000.00 | A010 | |
| C00008 | Karolina | Torento | Torento | Canada | NULL | 7000.00 | 7000.00 | 9000.00 | 5000.00 | A004 | |
| C00009 | Ramesh | Mumbai | Mumbai | India | NULL | 8000.00 | 7000.00 | 3000.00 | 12000.00 | A002 | |
| C00010 | Charles | Hampshair | Hampshair | UK | NULL | 6000.00 | 4000.00 | 5000.00 | 5000.00 | A009 | |
| C00011 | Sundariya | Chennai | Chennai | India | NULL | 7000.00 | 11000.00 | 7000.00 | 11000.00 | A010 | |
| C00012 | Steven | San Jose | San Jose | USA | NULL | 5000.00 | 7000.00 | 9000.00 | 3000.00 | A012 | |
| C00013 | Holmes | London | London | UK | NULL | 6000.00 | 5000.00 | 7000.00 | 4000.00 | A003 | |
| C00014 | Rangarappa | Bangalore | Bangalore | India | NULL | 8000.00 | 11000.00 | 7000.00 | 12000.00 | A001 | |
| C00015 | Stuart | London | London | UK | NULL | 6000.00 | 8000.00 | 3000.00 | 11000.00 | A003 | |
| C00016 | Venkatpati | Bangalore | Bangalore | India | NULL | 8000.00 | 11000.00 | 7000.00 | 12000.00 | A007 | |
| C00017 | Srinivas | Bangalore | Bangalore | India | NULL | 8000.00 | 4000.00 | 3000.00 | 9000.00 | A007 | |
| C00018 | Fleming | Brisban | Brisban | Australia | NULL | 7000.00 | 7000.00 | 9000.00 | 5000.00 | A005 | |
| C00019 | Yearannaidu | Chennai | Chennai | India | NULL | 8000.00 | 7000.00 | 7000.00 | 8000.00 | A010 | |
| C00020 | Albert | New York | New York | USA | NULL | 5000.00 | 7000.00 | 6000.00 | 6000.00 | A008 | |
| C00021 | Jacks | Brisban | Brisban | Australia | NULL | 7000.00 | 7000.00 | 7000.00 | 7000.00 | A005 | |
| C00022 | Avinash | Mumbai | Mumbai | India | NULL | 7000.00 | 11000.00 | 9000.00 | 9000.00 | A002 | |
| C00023 | Karl | London | London | UK | NULL | 4000.00 | 6000.00 | 7000.00 | 3000.00 | A006 | |
| C00024 | Cook | London | London | UK | NULL | 4000.00 | 9000.00 | 7000.00 | 6000.00 | A006 | |
| C00025 | Ravindran | Bangalore | Bangalore | India | NULL | 5000.00 | 7000.00 | 4000.00 | 8000.00 | A011 |
Here is the script:
DECLARE @TableName NVARCHAR(255) = 'CUSTOMER';
DECLARE @ColumnName NVARCHAR(255), @SQL NVARCHAR(MAX) = '';
DECLARE ColumnCursor CURSOR FOR
SELECT c.name
FROM sys.columns c
INNER JOIN sys.tables t ON c.object_id = t.object_id
WHERE t.name = @TableName
OPEN ColumnCursor;
FETCH NEXT FROM ColumnCursor INTO @ColumnName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'IF NOT EXISTS (SELECT 1 FROM ' + @TableName + ' WHERE LEN([' + @ColumnName + ']) > 0) PRINT ''' + @ColumnName + '''';
EXEC sp_executesql @SQL;
FETCH NEXT FROM ColumnCursor INTO @ColumnName;
END
CLOSE ColumnCursor;
DEALLOCATE ColumnCursor;
And here is the result from running the script. You can see the two columns with NULL and empty values listed:
There are, of course, many ways to solve this problem, but this solution works, so I'll stick with this.
I have tested and verified this script on Microsoft SQL Server versions 2008, 2012, 2014, 2016, 2017, and 2019.