SQL Server - How to find all empty/have values tables


Many times our managers ask to find empty tables and have value tables report. It is time taken to find the tables manually. I am writing some queries which may help you to find empty and have a values table.
The following query helps you to find all empty tables in the database.

1 – See the following query


SELECT IST.TABLE_NAME , MAX(SI.rows ) TOTAL_RECORDS
FROM sysindexes SIINFORMATION_SCHEMA.TABLES IST
WHERE IST.TABLE_NAME = OBJECT_NAME(SI.id)
      AND IST.TABLE_TYPE = 'BASE TABLE'
GROUP BY IST.TABLE_SCHEMAIST.TABLE_NAME
HAVING MAX(SI.rows)<=0

  
2- If you want to find all empty tables of particular “schema” then use the following query.


SELECT IST.TABLE_NAME , MAX(SI.rows ) TOTAL_RECORDS
FROM sysindexes SIINFORMATION_SCHEMA.TABLES IST
WHERE IST.TABLE_NAME = OBJECT_NAME(SI.id)
      AND IST.TABLE_TYPE = 'BASE TABLE'
      AND IST.TABLE_SCHEMA='Schema_Name'
GROUP BY IST.TABLE_SCHEMAIST.TABLE_NAME
HAVING MAX(SI.rows)<=0


If you want to find those all tables that have values, then use the following queries.
1 – Query to find all tables that have values.


SELECT IST.TABLE_NAME , MAX(SI.rows ) TOTAL_RECORDS
FROM sysindexes SIINFORMATION_SCHEMA.TABLES IST
WHERE IST.TABLE_NAME = OBJECT_NAME(SI.id)
      AND IST.TABLE_TYPE = 'BASE TABLE'
GROUP BY IST.TABLE_SCHEMAIST.TABLE_NAME
HAVING MAX(SI.rows)>0


2- If you want to find all tables that have values of particular “schema,” then use the following query.


SELECT IST.TABLE_NAME , MAX(SI.rows ) TOTAL_RECORDS
FROM sysindexes SIINFORMATION_SCHEMA.TABLES IST
WHERE IST.TABLE_NAME = OBJECT_NAME(SI.id)
      AND IST.TABLE_TYPE = 'BASE TABLE'
      AND IST.TABLE_SCHEMA = 'SCHEMA_NAME'
GROUP BY IST.TABLE_SCHEMAIST.TABLE_NAME
HAVING MAX(SI.rows)>0


Another query may also help you to find all Empty tables.


;WITH TBL AS
(
   SELECT
      SUM(row_countAS ROW_COUNT,
      OBJECT_NAME(OBJECT_IDAS TABLE_NAME,
      OBJECT_SCHEMA_NAME(OBJECT_IDAS TABLE_SCHEMA
   FROM
      sys.dm_db_partition_stats
   WHERE
      INDEX_ID = 0 OR INDEX_ID= 1
         
   GROUP BY
      OBJECT_ID
)
SELECT *
FROM TBL
WHERE ROW_COUNT = 0

  
You can filter for schema as below.


;WITH TBL AS
(
   SELECT
      SUM(row_countAS ROW_COUNT,
      OBJECT_NAME(OBJECT_IDAS TABLE_NAME,
      OBJECT_SCHEMA_NAME(OBJECT_IDAS TABLE_SCHEMA
   FROM
      sys.dm_db_partition_stats
   WHERE
      INDEX_ID = 0 OR INDEX_ID= 1
         
   GROUP BY
      OBJECT_ID
)
SELECT *
FROM TBL
WHERE ROW_COUNT = 0
AND TABLE_SCHEMA='Schema_Name'


If you wish to find all tables which have values so please change in above query ROW_COUNT >0


Related Posts

What is the Use of isNaN Function in JavaScript? A Comprehensive Explanation for Effective Input Validation

In the world of JavaScript, input validation is a critical aspect of ensuring that user-provided data is processed correctly. One indispensa...