About This Blog

.net & SQL Samples, programming tips and tricks, performance tips, guidelines, and best practices

Friday 6 May 2011

Find all empty tables in SQL Server Database


Yesterday I was working on one of my Databases and wanted to find out all the empty tables in that. Suddenly, I realized that there is no direct way to get a list of all those tables; even SSMS also doesn’t help much for this.

After googling for a few minutes, I decided to use one of the undocumented stored procedure - sp_Msforeachtable.

To get the list of empty tables, we can use the below tsql –
EXEC sp_MSforeachtable 'IF NOT EXISTS (SELECT 1 FROM ?) PRINT ''?'' '

And, to get a list of tables having at least one row of data, we can use the below tsql –
EXEC sp_MSforeachtable 'IF EXISTS (SELECT 1 FROM ?) PRINT ''?'' '

Please note that the sp_MSforeachtable stored procedure is undocumented, so be careful about using this stored procedure in production code.

No comments:

Post a Comment