Tuesday, June 11, 2013

Find empty tables in SQL Database

Hi friends,

Here is the SQL code to find the Table Names and their owner of the Database where 0 records are available.

;With CTE(TableName, SchemaName, RowCnt)
As
(
 Select
T.Name As TableName, S.name As SchemaName, R.RowCnt
 from Sys.tables As T Inner Join
 (
Select
 ID, rowcnt As RowCnt
From SysIndexes Where IndId < 2
 ) As R On T.[Object_Id] = R.Id
 Inner Join sys.schemas As S On S.[schema_id] = T.[schema_id]
)

Select
 TableName, SchemaName
From CTE Where RowCnt = 0