-- Stored Procedure: dbo.EmptyTablesByDB
-- Bill Wunder

use admin
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EmptyTablesByDB]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[EmptyTablesByDB]
GO

create procedure dbo.EmptyTablesByDB
as

If object_id('admin.dbo.EmptyTablesByDBExcludeList','U') is null
 begin
  create table admin.dbo.EmptyTablesByDBExcludeList (DBName varchar(128), TableName varchar(128)) 
  exec sp_msforeachdb 'use ? 
   if not exists (select 1 
    from admin.dbo.EmptyTablesByDBExcludeList
    where DBname = db_name()
    and TableName = ''dtproperties'')
   insert admin.dbo.EmptyTablesByDBExcludeList values (db_name(),''dtproperties'')'
 end
exec sp_msforeachdb 'use ? 
if exists (select 1 from sysindexes i
  where i.rowcnt = 0 
  and i.indid in (0,1) 
  and objectproperty(i.id, ''IsSystemTable'') = 0
  and db_name() not in (''master'',''model'',''msdb'',''tempdb'')
  and not exists(select 1 from admin.dbo.EmptyTablesByDBExcludeList
    where DBname = db_name()
    and TableName = object_name(i.id)))
 select cast(object_name(i.id) as varchar(60)) as [tables with zero rows in Database ?] 
 from dbo.sysindexes i
 where i.rowcnt = 0 
 and i.indid in (0,1) 
 and objectproperty(i.id, ''IsSystemTable'') = 0
 and not exists(select 1 from admin.dbo.EmptyTablesByDBExcludeList
   where DBname = ''?''
   and TableName = object_name(i.id))'




GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO