SQL Server 2008: I have 1000 tables, I need to know which tables have data

Question

Is there a way in SMSS to detect whether a table has any records? I need to get a list of tables that have records. perhaps there is a sql statement that will do the trick?

Answer 1

Try this - gives you the table name and the row count:

SELECT      t.NAME AS TableName,     SUM(p.rows) AS [RowCount] FROM      sys.tables t INNER JOIN           sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN      sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id WHERE        i.index_id <= 1 GROUP BY      t.NAME, i.object_id, i.index_id, i.name  ORDER BY      SUM(p.rows) DESC 

It shows all tables and their row counts in a single output.