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

You can use this stored procedure:

EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

This will return a resultset for each table in the database (each showing the name, and the number of rows, among other information).

Here is how you can put them into a table variable, and order them by the number of rows:

DECLARE @TBL TABLE (
    [name] nvarchar(500),
    [rows] bigint,
    [reserved] nvarchar(500),
    [data] nvarchar(500),
    [index_size] nvarchar(500),
    [unused] nvarchar(500)
)

INSERT INTO @TBL
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

SELECT * FROM @TBL
ORDER BY [rows] DESC

Answer 2

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.

Answer 3

As your question specifically mentions SSMS you can also right click the database in object explorer and then from the short cut menu do

Reports -> Standard Reports -> Disc Usage By Table

Answer 4

A simpler syntax:

SELECT  [Name]      = o.name
    ,     [RowCount]  = SUM(p.row_count)

FROM    SYS.DM_DB_PARTITION_STATS p

INNER JOIN
        SYS.TABLES o
    ON p.[object_ID] = o.[object_id]

WHERE   index_id    <= 1 -- Heap or clustered index only

GROUP BY o.name

ORDER   BY 2 desc