Count empty tables in SQL Server database

1123735 pts.
Tags:
SQL Server
SQL Server database
T-SQL
In SQL Server, would there be a way for me to count the tables with no rows? Can I use a T-SQL statement? Thank you very much.
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

Try this

;WITH EmptyRows AS 
( 
   SELECT SUM(row_count) AS [TotalRows], 
          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 EmptyRows 
WHERE [TotalRows] = 0

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • ToddN2000
    This may also work for you

    SELECT
    t.NAME AS TableName, p.rows AS RowCounts FROM sys.tables t INNER JOIN sys.partitions p ON t.object_id = p.OBJECT_ID WHERE t.NAME NOT LIKE 'dt%' AND t.is_ms_shipped = 0 AND p.rows = 0 GROUP BY t.Name, p.Rows ORDER BY t.Name
    125,530 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: