Select all empty tables in SQL Server
select a.rows as Rowcnt,
b.name as Tbl_Name
from sys.partitions a
join sys.tables b
on a.object_id=b.object_id
where b.type='u'
and a.rows = 0
SELECT name AS [TableList] FROM SYS.DM_DB_PARTITION_STATS s
INNER JOIN sys.tables t ON t.[object_id] = s.[object_id]
WHERE row_count = 0
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 ''?'' '
Note: List of table included only 'User Table' i.e. Not included 'System Table'.
On SQL Server 2005 and up, you can use something like this:
;WITH TableRows AS
(
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.
UPDATE: if you want to check for non-Microsoft / system tables, you need to extend the query like this (joining the sys.tables
catalog view):
;WITH TableRows AS
(
SELECT
SUM(ps.row_count) AS [RowCount],
t.Name AS TableName
FROM
sys.dm_db_partition_stats ps
INNER JOIN
sys.tables t ON t.object_id = ps.object_id
WHERE
(ps.index_id = 0 OR ps.index_id = 1)
AND t.is_ms_shipped = 0
GROUP BY
t.Name
)
SELECT *
FROM TableRows
WHERE [RowCount] = 0