Create proc USP_listobject @dbname varchar(200)='%' as
--Script Language and Platform:MS SQL 2000
--Objective: List all the objects in all the databases,certain databases
--Author: Claire Hsu
--Objective: To display all the objects from the list of databases
--Date: May 20,2003
--Email: messageclaire@yahoo.com
set nocount on
set @dbname = replace(@dbname,'[','')
set @dbname = replace(@dbname,']','')
declare @string varchar(50)
declare @db varchar(50)
Create table #object_dis (name sysname,xtype char(20),DBname varchar(200))
declare cur1 cursor for select '['+name+']' from sysdatabases
open cur1
fetch next from cur1 into @db
while @@fetch_status = 0
begin
set @string = 'use '+@db
exec(@string)
set @db = replace(@db,'[','')
set @db = replace(@db,']','')
insert into #object_dis select name,xtype,@db as dbname from sysobjects 

fetch next from cur1 into @db
end
if @dbname <>'%' and @dbname <>''
begin
set @dbname = replace(@dbname,',','%" or dbname like "')
exec('
select name,
(case xtype when "c" then "CHECK constraint" when "D" then "Default" when "F" then "FOREIGN KEY"
when "FN" then "Scalar function" when "IF" then "Inlined table-function" when "K" then "Primary Key"
when "L" then "Log" when "P" then "Stored procedure" when "R" then "Rule" when "RF" then "Replication filter stored procedure"
when "PK" then "PRIMARY KEY" when "S" then "System table" when "TF" then "Table function" when "TR" then "Trigger" when "U" then "User table"
when "UQ" then "UNIQUE constraint" when "U" then "User Table"
when "V" then "VIEW" when "X" then "Extended stored procedure" end) as "xtype"
,dbname
from #object_dis where dbname like "'+@dbname+'%" group by dbname,xtype,name ')
end
if @dbname = '%' or @dbname = ''
exec('select name,
(case xtype when "c" then "CHECK constraint" when "D" then "Default" when "F" then "FOREIGN KEY"
when "FN" then "Scalar function" when "IF" then "Inlined table-function" when "K" then "Primary Key"
when "L" then "Log" when "P" then "Stored procedure" when "R" then "Rule" when "RF" then "Replication filter stored procedure"
when "PK" then "PRIMARY KEY" when "S" then "System table" when "TF" then "Table function" when "TR" then "Trigger" when "U" then "User table"
when "UQ" then "UNIQUE constraint" when "U" then "User Table"
when "V" then "VIEW" when "X" then "Extended stored procedure" end) as "xtype",
dbname from #object_dis where dbname like "%" group by dbname,xtype,name')
drop table #object_dis
close cur1
deallocate cur1

--Usage
--exec USP_listobject
--exec USP_listobject''
--exec USP_listobject 'msdb'
--exec USP_listobject '[ma]'
--exec USP_listobject 'ma'
--exec USP_listobject 'ma'
--exec USP_listobject 'claire,ma'
--exec USP_listobject '[claire],ma'
--exec USP_listobject '[I am ],master' 
--exec USP_listobject '[I am ],[ms]'