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