22

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Alex Gordon
  • 57,446
  • 287
  • 670
  • 1,062
  • https://stackoverflow.com/questions/17748417/sql-server-management-studio-finding-all-non-empty-tables/50602998#50602998 – jophab May 30 '18 at 11:35

5 Answers5

29

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • How can it be restricted to certain DB? If I have on the same sql server more databases with the same table names, the row count will be added together. – Ondrej Peterka Nov 27 '15 at 15:01
  • @OndraPeterka: no, this is always executed in the context of **one database** - and only those tables will be shown. This does **not** show all tables of all databases of a server – marc_s Nov 27 '15 at 15:53
  • I'm using it and it's bringing back data from every database – Don P Apr 25 '17 at 14:42
21

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
Hugo Forte
  • 5,718
  • 5
  • 35
  • 44
Zephyr was a Friend of Mine
  • 7,661
  • 4
  • 37
  • 39
19

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

Screenshot

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
3

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
Gabriel McAdams
  • 56,921
  • 12
  • 61
  • 77
1

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!!!

Nitika Chopra
  • 1,281
  • 17
  • 22