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 ''?'' '
It works. Thanks.
ReplyDelete;WITH TableRows AS
ReplyDelete(
SELECT
SUM(row_count) AS [RowCount],
OBJECT_NAME(OBJECT_ID) AS TableName
FROM
sys.dm_db_partition_stats
WHERE
index_id = 0 OR index_id = 1
GROUP BY
OBJECT_ID
)
SELECT *
FROM TableRows
WHERE [RowCount] = 0
The inner select in the CTE (Common Table Expression) calculates the number of rows for each table and groups them by table (OBJECT_ID), and the outer SELECT from the CTE then grabs only those rows (tables) which have a total number of rows equal to zero.