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.

Answer 2

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

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

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 5

Hope, It helps you-

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

This code shows that list of tables, which does not contain any data or row.

Thanks!!!