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






CREATE PROCEDURE [dbo].[LargestTables]    
AS
/*******************************************************************************************************
* admin.dbo.LargestTables 
* Creator:       Bill Wunder
* Date:          
*
* Description: Get a result set of largest tables by database 
* Notes:         
*
* Usage:
      EXECUTE admin.dbo.LargestTables
*
*
* Modifications:   
*          Developer  Date     Brief Description
*          ---------- -------- ------------------------------------------------------------
*     bw       5-15-03  remove row count because we do FAST showcontig (no page level scan)                    
********************************************************************************************************/

---------------------------------------------
-- declare variables
---------------------------------------------

---------------------------------------------
-- create temp tables
---------------------------------------------

---------------------------------------------
-- set session variables
---------------------------------------------
SET NOCOUNT ON


---------------------------------------------
-- body of stored procedure
---------------------------------------------

exec sp_msforeachdb 'use ? 
if ''?'' not in (''master'',''model'',''msdb'',''NorthWind'',''pubs'',''tempdb'')
BEGIN
 exec sp_spaceused
 select top 10 cast(object_name(i.id) as varchar(40)) [Ten Largest Tables], 
  cast(i.rowcnt as int) [Rows], 
  case when f1.CountPages is null
   then i.dpages
   else f1.CountPages
   end * cast(8000 as bigint) [Bytes Used],
  case when f1.CountPages is null or f2.CountPages is null
   then ''unknown''
   else cast((f1.CountPages - f2.CountPages)*100/f1.CountPages as varchar(10)) + ''%''
   end [Recent Growth],
  case when f1.ScanDensity is null
   then case when i.indid = 0 then '' '' else ''unknown'' end
   else cast(100 - f1.ScanDensity as varchar(5)) + ''%''
   end [pct Fragmented],
  case when i.indid = 1 
   then ''clustered'' 
   else ''heap''
   end [Storage Type]
 from sysindexes i
 left join admin.dbo.fraglist f1
 on object_name(i.id) = object_name(f1.ObjectId)
 and i.indid = f1.indexid
 and f1.DatabaseName = ''?''
 and f1.status < 10
 left join admin.dbo.fraglist f2
 on f1.ObjectName = f2.ObjectName
 and f1.DatabaseName = f2.DatabaseName
 and f1.indexid = f2.indexid
 and cast(cast(f1.RecCreatedDt as varchar(11)) as datetime) 
  between cast(cast(f2.RecCreatedDt + 5 as varchar(11)) as datetime) 
  and cast(cast(f2.RecCreatedDt + 10 as varchar(11)) as datetime)
 where i.indid < 2 
 and object_name(i.id) not like ''sys%''
 and object_name(i.id) not like ''dt%''
 and i.rowcnt > 0
 order by cast(i.rowcnt as int) desc
print ''==========================================================''
print ''''
END'

RETURN




GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO