-- 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