use admin
GO
SET QUOTED_IDENTIFIER OFF 
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
/*******************************************************************************************************
*	admin.dbo.EmptyTablesByDB
*	Creator:	Bill Wunder 
*
*       Date:           3-13-2003 
*
*	Project:	utility
*
*	Description:	Get a list of tables with no rows by database that have been empty since
*                       the last defrag maintenance was run on the table.  
*
*	Usage:
	     EXECUTE admin.dbo.EmptyTablesByDB
*       notes: may want to exclude ETL staging databases
*
*	Modifications:   
*         Developer Name     Date     Brief Description
*         ------------------ -------- ------------------------------------------------------------
*
********************************************************************************************************/

exec sp_msforeachdb 'if ''?'' not in (''master'',''model'',''msdb'',''tempdb'')
	begin
		use ? 
		if exists (select 1 from sysindexes i
				inner join admin.dbo.fraglist f
				on i.id = f.objectid
				and i.indid = f.indexid
				where i.rowcnt = 0 
				and i.indid in (0,1) 
				and i.id > 100
				and object_name(i.id) <> ''dtproperties''
				and f.DatabaseName = ''?''
				and f.status < 10)

			select cast(object_name(i.id) as varchar(60)) as [tables with zero rows in Database ''?''] 
			from dbo.sysindexes i
			inner join admin.dbo.fraglist f
			on i.id = f.objectid
			and i.indid = f.indexid
			where i.rowcnt = 0 
			and i.indid in (0,1) 
			and i.id > 100
			and object_name(i.id) <> ''dtproperties''
			and f.DatabaseName = ''?''
			and f.status < 10
end'





GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO