Finding unused fields & empty tables in an SQL database
Empty is simple Windows™ utility which helps you find unused fields and empty tables within a Microsoft SQL Server database.
If your looking for an audit tool which lists all unused fields and empty tables within a production Microsoft SQL database, then go grab the download, if you want to learn the SQL code I used to write this tool – then read on.
In readiness for an ALMA redesign I have been reviewing the current database schema and realised that some fields had been originally defined, but due to design decisions made during the evolution of the product, these fields where never populated with data.
I couldn’t be sure which fields hadn’t been used, so I wrote this little utility application to find all empty tables and fields.
The SQL to find empty fields
I used the SQL statement below to list all the tables in the specified database. Ordering them by name, ensuring that the finished report is listed in alphabetic order.
SELECT name FROM dbo.sysobjects WHERE xtype = 'U' ORDER BY name
Assigning the results of this query to a record set, it was then easy to loop through all the tables in order and check to see if the table was completely empty by using the following SQL statement.
SELECT COUNT(*) AS Total FROM [A]
While looping through all the table names, we substitute A for the table name we are currently checking.
If the table did in fact have records, then the next step was to loop through each of the fields in the table and determine if they contained any data. The SQL statement shown below demonstrates how to query the list of fields for a given table. Substitute
A for the table name you want to get a list of fields for.
SELECT name FROM dbo.syscolumns WHERE id = object_id('[A]') ORDER BY colid
I tried various SQL statements to check if the field was in fact empty, and depending on the size of database and whether the field was indexed or not, the query below seemed to produce the fastest results. It’s returning 1 row if at least 1 record has data in the current field being tested. If the query returns no rows, then the field must be empty. Substitute
A for the table name and
B for the field name.
SELECT TOP 1 1 AS Total FROM [A] WHERE ([B] IS NOT NULL)
Report showing unused fields and empty tables
The utility writes the results to an HTML file, which is loaded into your default browser at the end of the process. Below is a sample screen shot showing a finished report.
You can download the latest version of this utility free of charge as it’s released under an MIT license. Simply unzip the file to a folder and double click the Empty.exe file to start. Enter your SQL server/instance name and the database name you want to check. As long as you have the necessary rights to access the database via Windows authentication, then the utility will loop through all the tables and fields and report on which fields are not actually populated with data.
RELEASE : 23.May.2016 VERSION : 1.0 MD5 HASH : efdf94ad87edd167af66d3e8bd6619bf SHA1 HASH : 7b73106be773affd58df88aacba018fbb8a02cdd