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


create procedure dbo.MostRowsByDB
 @recipients varchar(100) = 'bill.wunder@wallst.com'
as
declare @message varchar(1024)
set nocount on
set @message = 'The report is a union of the 10 largest tables and all tables with over 1M rows.

The column [Rows Now] is current row count while [Rows Sat] comes from the maintenance window at the weekend. Use to identify changing tables.

[CountPages] shows the space in 8K pages used by the table.

[Page Density] and [Scan Density] should approach 100%. Lower values suggest table changes work against the clustered index. 

The column [Storage Type] indicates table has clustered index. 

Additional details by table can be found in table admin.dbo.fraglist or through the system stored procedures sp_helpindex and sp_help as well as in the Sourcesafe database:
 \\MERCURY\DEVELOP\SS  
under project: 
 $/SQL/DBA/PULL/' + upper(@@servername) + '/<database>/<owner>.<table>.TAB'

exec sysmon.dbo.safe_sendmail @recipients = 'bill.wunder@wallst.com',
@subject = 'The tables with the most rows in each db',
@message = @message,

@query = 'sp_msforeachdb ''use ? 
if ''''?'''' not in (''''master'''',''''model'''',''''msdb'''',''''NorthWind'''',''''pubs'''',''''tempdb'''')
select top 10 cast(object_name(i.id) as varchar(30)) as [Most rows in database ?], 
 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''
union 
select cast(object_name(i.id) as varchar(30)) as [Most rows in database ?], 
 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 i.rowcnt desc''
',
@width = 140





GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON 
GO