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].[LargestTables]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[LargestTables]
GO


create procedure dbo.LargestTables
as
/*******************************************************************************************************
*	admin.dbo.LargestTables
*	Creator:	Bill Wunder 
*
*       Date:           3-13-2003 
*
*	Project:	utility
*
*	Description:	Get a list of the largets tables in each database
*
*	Usage:
	     EXECUTE admin.dbo.LargestTables
*       notes:
*
*	Modifications:   
*         Developer Name     Date     Brief Description
*         ------------------ -------- ------------------------------------------------------------
*
********************************************************************************************************/

exec sp_msforeachdb 'use ? 
if ''?'' not in (''master'',''model'',''msdb'',''NorthWind'',''pubs'',''tempdb'')
	if (select count(*) from sysindexes where indid in (0,1) and rowcnt > 1000000) < 10
		select top 10 cast(object_name(i.id) as varchar(30)) as [Tables in db ?],
 			cast(i.rowcnt as int) as [Rows Now], 
			f.CountRows [Rows Sat], 
			f.CountPages, 
			AvgRecSize, 
			AvgPageDensity, 
			cast(ScanDensity as tinyint) as [ScanDensity],
			case when indid = 1 
				then ''clustered''
				else ''heap''
			end as [Storage Type]
		from sysindexes i
		inner join admin.dbo.fraglist f
		on object_name(i.id) = object_name(f.ObjectId)
		and i.indid = f.indexid
		where i.indid in (1,0) 
		and f.DatabaseName = ''?''
		and object_name(i.id) not like ''sys%''
		and object_name(i.id) not like ''dt%''
		and i.rowcnt > 0
		and f.status < 10
		order by i.rowcnt desc
	else
		select cast(object_name(i.id) as varchar(30)) as [Tables in db ?], 
			cast(i.rowcnt as int) as [Rows Now], 
			f.CountRows [Rows Sat], 
			f.CountPages, 
			AvgRecSize, 
			AvgPageDensity, 
			cast(ScanDensity as tinyint) as [ScanDensity],
			case when indid = 1 
				then ''clustered'' 
				else ''heap''
			end as [Storage Type]
		from sysindexes i
		inner join admin.dbo.fraglist f
		on object_name(i.id) = object_name(f.ObjectId)
		and i.indid = f.indexid
		where i.indid in (1,0) 
		and f.DatabaseName = ''?''
		and object_name(i.id) not like ''sys%''
		and object_name(i.id) not like ''dt%''
		and i.rowcnt > 1000000
		and f.status < 10
		order by cast(i.rowcnt as int) desc'




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO