create proc Find_Object(@Object_name varchar(100),@xtype varchar(2))
as
/*
@Object_name 对象名
@xtype 对象的类型
对象类型。可以是下列对象类型中的一种:
C = CHECK 约束
D = 默认值或 DEFAULT 约束
F = FOREIGN KEY 约束
L = 日志
FN = 标量函数
IF = 内嵌表函数
P = 存储过程
PK = PRIMARY KEY 约束(类型是 K)
RF = 复制筛选存储过程
S = 系统表
TF = 表函数
TR = 触发器
U = 用户表
UQ = UNIQUE 约束(类型是 K)
V = 视图
X = 扩展存储过程
*/
declare @dbname sysname,@sql nvarchar(4000),@re bit,@sql1 varchar(8000)
set @sql1=''
declare tb cursor for select name from master..sysdatabases
open tb
fetch next from tb into @dbname
while @@fetch_status=0
begin
set @sql='set @re=case when exists(select 1 from ['
+@dbname+']..sysobjects where xtype=''' + @xtype + ''' and name='''
+@Object_name+''') then 1 else 0 end'
exec sp_executesql @sql,N'@re bit out',@re out
if @re=1 set @sql1=@sql1+' union all select '''+@dbname+''''
fetch next from tb into @dbname
end
close tb
deallocate tb
set @sql1=substring(@sql1,12,8000)
exec(@sql1)
-------Test
exec Find_Object 'P_indent','P'