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.