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