How to find list of tables having no records

use test

GO

SELECT

  OBJECT_SCHEMA_NAME(OBJECT_ID) AS [Schema Name]

, OBJECT_NAME(OBJECT_ID) As [Table Name]

, SUM([rows]) as [Total Records] FROM sys.partitions

WHERE OBJECTPROPERTYEX (OBJECT_ID, N'IsUserTable')=1

--Remove the above where clause,

--if you need to view system objects as well.

AND [index_id] <=1

GROUP BY OBJECT_ID

HAVING SUM([rows])=0

ORDER BY OBJECT_NAME(OBJECT_ID)



















or



















use test GO SELECT OBJECT_SCHEMA_NAME(p.OBJECT_ID) AS [Schema Name] , OBJECT_NAME(p.OBJECT_ID) As [Table Name] , SUM([rows]) as [Total Records] FROM sys.partitions p inner join sys.tables t on p.object_id=t.object_id WHERE-- OBJECTPROPERTYEX (OBJECT_ID, N'IsUserTable')=1 AND --Remove the above where clause, --if you need to view system objects as well. [index_id] <=1 GROUP BY p.OBJECT_ID HAVING SUM([rows])=0 ORDER BY OBJECT_NAME(p.OBJECT_ID)










http://mssqldevelopersaran.blogspot.com