Finding out which tables in a SQL database have no rows

How many times have you looked at a production database and found a table that has no rows in it? Then, when questioning the developers, you are told, “Oh, that table was never used.” (That’s slack undertow.) It happens to me often enough, so I wanted to come up with a way to easily find all the tables in a SQL database that have no rows.

One line of SQL (which actually runs three lines of SQL, once for each table in the database) is all you need:

exec sp_MSforeachtable 'declare @count int; select @count = count(*) from ?; if @count = 0 print ''?'';'

Run that, and the output will list the schema and table name of every table in the current database that has no rows.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This site uses Akismet to reduce spam. Learn how your comment data is processed.