Counts of database objects

-- sql 2005 - database object counts

if object_id('tempdb..#objecttypes') is not null
begin
drop table #objecttypes
end
create table #objecttypes (otype char(5)collate Latin1_General_CI_AS ,typedesc varchar(50),primary key clustered(otype))

insert into #objecttypes(otype,typedesc) values('AF','Aggregate function (CLR)')
insert into #objecttypes(otype,typedesc) values('C','Check constraint')
insert into #objecttypes(otype,typedesc) values('D','Default (constraint or stand-alone)')
insert into #objecttypes(otype,typedesc) values('F','Foreign Key constraint')
insert into #objecttypes(otype,typedesc) values('PK','Primary Key constraint')
insert into #objecttypes(otype,typedesc) values('P','SQL Stored procedure')
insert into #objecttypes(otype,typedesc) values('PC','Assembly (CLR) stored procedure')
insert into #objecttypes(otype,typedesc) values('FN','SQL scalar function')
insert into #objecttypes(otype,typedesc) values('FS','Assembly (CLR) scalar function')
insert into #objecttypes(otype,typedesc) values('FT','Assembly (CLR) table-valued function')
insert into #objecttypes(otype,typedesc) values('R','Rule (old-style, stand-alone)')
insert into #objecttypes(otype,typedesc) values('RF','Replication-filter-procedure')
insert into #objecttypes(otype,typedesc) values('S','System base table')
insert into #objecttypes(otype,typedesc) values('SN','Synonym')
insert into #objecttypes(otype,typedesc) values('SQ','Service queue')
insert into #objecttypes(otype,typedesc) values('TA','Assembly (CLR) DML trigger')
insert into #objecttypes(otype,typedesc) values('TR','SQL DML trigger')
insert into #objecttypes(otype,typedesc) values('IF','SQL inline table-valued function')
insert into #objecttypes(otype,typedesc) values('TF','SQL table-valued-function')
insert into #objecttypes(otype,typedesc) values('U','Table (user-defined)')
insert into #objecttypes(otype,typedesc) values('UQ','Unique constraint')
insert into #objecttypes(otype,typedesc) values('V','View')
insert into #objecttypes(otype,typedesc) values('X','Extended stored procedure')
insert into #objecttypes(otype,typedesc) values('IT','Internal table')

select otype,typedesc,count(s.type) as total
from #objecttypes
left join sys.objects s
on #objecttypes.otype collate Latin1_General_CI_AS = s.type collate Latin1_General_CI_AS
group by otype, typedesc