CODEWITHSUNDEEP
sql-servert-sql
David Kethel
David Kethel

Reputation: 2550

Select all empty tables in SQL Server

How to get the list of the tables in my sql-server database that do not have any records in them?

Upvotes: 34

Views: 45984

Answers (6)

Vinit Raypure
Vinit Raypure

Reputation: 1

  1. We can find all empty tables by using below code.
select schema_name(tab.schema_id) + '.' + tab.name as [TableList] 
from sys.tables tab 
inner join sys.partitions part 
on tab.object_id = part.object_id 
where part.index_id IN (1, 0) 
group by schema_name(tab.schema_id) + '.' + tab.name 
having sum(part.rows) = 0 
order by [TableList]

Upvotes: 0

marc_s
marc_s

Reputation: 754578

On SQL Server 2005 and up, you can use something like this:

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

The inner select in the CTE (Common Table Expression) calculates the number of rows for each table and groups them by table (OBJECT_ID), and the outer SELECT from the CTE then grabs only those rows (tables) which have a total number of rows equal to zero.

UPDATE: if you want to check for non-Microsoft / system tables, you need to extend the query like this (joining the sys.tables catalog view):

;WITH TableRows AS
(
   SELECT 
       SUM(ps.row_count) AS [RowCount], 
       t.Name AS TableName
   FROM 
       sys.dm_db_partition_stats ps
   INNER JOIN
       sys.tables t ON t.object_id = ps.object_id
   WHERE 
       (ps.index_id = 0 OR ps.index_id = 1)
       AND t.is_ms_shipped = 0
   GROUP BY 
       t.Name
)
SELECT *
FROM TableRows
WHERE [RowCount] = 0

Upvotes: 49

jophab
jophab

Reputation: 5509

We can simply classify Tables into two types.

  1. Clustered Table ( Tables having a Clustered Index )
  2. Heap Tables ( Tables not having a Clustered Index )

In SQL Server, all tables are divided into partitions. There will be at least one partition for each table .

In sys.partitions, there exists one row for each partition of all tables.

The entries in sys.partitions contains a column for number of rows in that partition of the corresponding table.

Since all tables in SQL Server contain aleast one partition, we can get the information about number of rows in a table from sys.partitions.

SELECT
        OBJECT_NAME(T.OBJECT_ID) AS TABLE_NAME,
        SUM(P.ROWS)  AS TOTAL_ROWS
FROM
        SYS.TABLES T
INNER JOIN 
        SYS.PARTITIONS P 
        ON T.OBJECT_ID = P.OBJECT_ID
WHERE 
        P.INDEX_ID IN (0,1)
GROUP BY 
        T.OBJECT_ID
HAVING 
        SUM(P.ROWS) = 0

While taking sum of rows in different partitions, we are considering index_id (0,1)

  • index_id = 0 for Heap
  • index_id = 1 for Clustered index
  • index_id > 1 are for nonclustered index.

A table can have either one clustered index or none.

But a table can have multiple nonclustered indexes. So we cannot use index_ids of nonclustered indexes while summing rows.

  • Heap Tables will be having index_id = 0
  • Clustered Tables will be having index_id = 1

Upvotes: 0

Sagar Mahajan
Sagar Mahajan

Reputation: 191

  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

Upvotes: 2

Rikin Patel
Rikin Patel

Reputation: 9383

To get the list of empty tables, we can use the below tsql –

EXEC sp_MSforeachtable 'IF NOT EXISTS (SELECT 1 FROM ?) PRINT ''?'' '

And, to get a list of tables having at least one row of data, we can use the below tsql –

EXEC sp_MSforeachtable 'IF EXISTS (SELECT 1 FROM ?) PRINT ''?'' '

Note: List of table included only 'User Table' i.e. Not included 'System Table'.

Upvotes: 26

T.S.Sathish
T.S.Sathish

Reputation: 79

select a.rows as Rowcnt,
   b.name as Tbl_Name 
from sys.partitions a
join sys.tables b
   on a.object_id=b.object_id
where b.type='u' 
   and a.rows = 0

Upvotes: 7

Related Questions