CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Sabith Paul
Sabith Paul

Reputation: 145

Find tables without data

How can we retrieve all tables in database without data (as in, there are no rows in table) in the case of a Microsoft SQL Server?
Is there any method?

Upvotes: 7

Views: 3139

Answers (5)

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

As stated in AdaTheDev's answer:

A statement I think is worth mentioning is SET FMTONLY:

SET FMTONLY ON;
SELECT * FROM SomeTable
SET FMTONLY OFF;

No rows are processed or sent to the client because of the request when SET FMTONLY is turned ON.

The reason this can be handy is because you can supply any query/stored procedure and return just the metadata of the resultset.

Or, as stated in Shoaib's answer

Try: SELECT TOP 0 * FROM [TableName]

and use SQLDataAdapter to fill a DataSet, then get the Table from that DataSet.

Fiddle Demo

Upvotes: 0

Azar
Azar

Reputation: 1867

Try this

   SELECT   TableName=OBJECT_NAME(OBJECT_ID) ,Data_Rows= SUM(row_count) 
   FROM     sys.dm_db_partition_stats
   WHERE    index_id in (0 ,1)
   GROUP BY OBJECT_ID
   HAVING   SUM(row_count)  = 0

OR If u need only user defined tables then use this

   SELECT TableName=OBJECT_NAME(s.OBJECT_ID) ,Data_Rows= SUM(row_count) 
   FROM     sys.dm_db_partition_stats s
   JOIN     sys.tables  T
   ON       T.object_id = S.object_id       
   WHERE    index_id in (0 ,1)
   and      T.type  = 'U'
   GROUP BY s.OBJECT_ID
   HAVING   SUM(row_count)  = 0

Upvotes: 3

Raj
Raj

Reputation: 10843

Try this -

WITH CTE AS
(
SELECT   sc.name +'.'+ ta.name TableName
         ,SUM(pa.rows) RowCnt
FROM     sys.tables ta
         INNER JOIN sys.partitions pa
                    ON pa.OBJECT_ID = ta.OBJECT_ID
         INNER JOIN sys.schemas sc
                    ON ta.schema_id = sc.schema_id
WHERE    ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
GROUP BY sc.name,ta.name
)

SELECT * FROM CTE WHERE RowCnt = 0

Upvotes: 1

Sagar Chavan
Sagar Chavan

Reputation: 525

SELECT '[' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + ']' AS fulltable_name, SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name,
i.rows
FROM sys.tables AS t INNER JOIN
sys.sysindexes AS i ON t.object_id = i.id AND i.indid < 2 and i.rows=0

It will give table name and rows in that tables

Upvotes: 1

Venkatesh Yeluri
Venkatesh Yeluri

Reputation: 41

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 ''?'' '

Upvotes: 0

Related Questions