vso vso - 4 months ago 6
SQL Question

How to check whether certain tables are empty?

How can I check whether certain datatables (e.g. "tableA", "tableB" & "tableC") are empty in one query (SQL Server)?

Expected result:
The most important for me is to have ONE result.


  • It could be true (if all tables are empty) vs false.

  • Another possibility is the sum of all entries over all tables like 0 (if all tables are empty) or otherwise n.



Example

There are three tables in database: table1, table2 and table3.


  1. All tables are empty. --> Expected result: 0

  2. Table1 has 3 rows, table2 has 0 rows, table3 has 1 row. --> Expected result: 4.


Answer

You also can use this DMV which will query the Count from CACHE only..

SELECT  SUM(row_count) AS rows
FROM sys.dm_db_partition_stats
where object_name(object_id) in ('table1','tab1e2')
group by OBJECT_NAME(object_id)

Update as per question:

;with cte
as
(SELECT  SUM(row_count) AS rows
        FROM sys.dm_db_partition_stats
        where object_name(object_id) in ('table1','tab1e2')
        group by OBJECT_NAME(object_id))
select sum(rows) as rows from cte
Comments