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