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

create procedure dbo.GetObjectCounts
as
/*******************************************************************************************************
*	admin.dbo.GetObjectCounts
*	Creator:	Bill Wunder 
*
*       Date:           3-13-2003 
*
*	Project:	utility
*
*	Description:	Get an aggregate count of the sql objects on a server
*                       
*
*	Usage:
	     EXECUTE admin.dbo.GetObjectCounts
*       notes: managers really like to see this one in their email box periodically
*              proc will create its tracking table if not already there
*
*	Modifications:   
*         Developer Name     Date     Brief Description
*         ------------------ -------- ------------------------------------------------------------
*
********************************************************************************************************/

declare @sqlstr nvarchar(4000), @CountDate int

set nocount on

if object_id('admin.dbo.ObjectsByDatabase','U') is null
	begin
		create table admin.dbo.ObjectsByDatabase
			(DatabaseName varchar(30) NOT NULL,
			ObjectType varchar(30) NOT NULL,
			CountDate int,
			ObjectCount int,
			constraint pk_ObjectsByDatabase__DatabaseName__ObjectType__CountDate primary key (DatabaseName, ObjectType, CountDate),
			constraint uk_ObjectsByDatabase__CountDate__DatabaseNAme__ObjectType unique (CountDate,DatabaseName, ObjectType))
	end

exec sp_msforeachdb 'declare @CountDate int
if ''?'' not in (''master'',''model'',''tempdb'',''msdb'')
	begin
		use ?

		set @CountDate = cast(getDate() as int)

		create table #ObjectsInThisDB
			(DatabaseName varchar(30), 
			ObjectType varchar(30),
			ObjectCount int,
			CountDate int)
		insert #ObjectsInThisDB 
			(DatabaseName, 
			ObjectType, 
			ObjectCount, 
			CountDate) 
		select 	db_name(), 
			substring(v.name,charindex('':'',v.name) + 1,datalength(v.name)), 
			count(xtype), 
			@CountDate
		from sysobjects o
		inner join master.dbo.spt_values v
		on o.xtype = substring(v.name,1,2)
		where v.type = ''O9T''
		group by v.name

		update d 
		set ObjectCount = t.ObjectCount
		from admin.dbo.ObjectsByDatabase d
		inner join #ObjectsInThisDB t
		on d.DatabaseName = t.DatabaseName
		and d.ObjectType = t.ObjectType
		and d.CountDate = t.CountDate

		insert admin.dbo.ObjectsByDatabase (DatabaseName, ObjectType, CountDate, ObjectCount)
		select db_name(), t.ObjectType, @CountDate, t.ObjectCount
		from #ObjectsInThisDB t
		where not exists (select 1 
				from admin.dbo.ObjectsByDatabase
				where DatabaseName = t.DatabaseName
				and objectType = t.ObjectType
				and CountDate = t.CountDate)
	end'		

delete admin.dbo.ObjectsByDatabase
where DatabaseName not in (select name from master.dbo.sysdatabases)

set @sqlstr = 'select cast(replace(objectType,''cns'',''constraint'') as varchar(50)) as [User Object Type], 
sum(ObjectCount) as [Count on ' + @@servername + ']
from admin.dbo.ObjectsByDatabase
where CountDate in (select max(CountDate) 
			from admin.dbo.ObjectsByDatabase
			Group by DatabaseName)
and objectType not like ''%system%''
group by ObjectType'

exec sp_executesql @sqlstr




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO