[原]在一堆数据库中找出特定的对象

在使用SQL Server 2005的性能报表进行调优的过程中，有些语句不知在哪个数据库中执行，可参看这篇文章[原]一次SQL Server调优经历 ，于是针对这个问题，我写了两段脚本用于按照对象名找出其所在的数据库。

在编写的过程中发现SQL Server 2005比SQL Server 2000在操作集合方面完善很多。第一段SQL Server 2005专用脚本使用了集合的交操作，非常简单地完成任务了，而第二段通用的就要借助临时表。

运行方式很简单，把“用户修改区域”里面改成你需要查找的对象名，然后把SQL脚本copy到查询分析器中运行就OK了。

例如：将用户修改区域改成：

set @target=N'
select ''aspnet_Paths'' union all
select ''aspnet_Profile''
';

执行脚本之后将会打印以下语句。

Object(s) Found In Database: MSPetShop4Services

 

SQL Server 2005:

--------------------说明-------------------
--在同一个实例的SQL Server 2005数据库
--中按照对象名找出其所在的数据库
--@taget为对象的名字集合，关系为'与'
--Powered by killkill
--------------------说明-------------------
use master;
declare @target varchar(max);
----------------用户修改区域---------------
--示例 Example
--set @target=N'
--select ''<tableName/viewName/spName/etc..>'' union all
--select ''<tableName/viewName/spName/etc..>''
--';

set @target=N'
select ''aspnet_Paths'' union all
select ''aspnet_Profile''
';

----------------用户修改区域---------------
declare @cmd varchar(max);
declare cur_cmd cursor
for
select 
'use '+ name+ ';'+
'if not exists ('+
@target+
'except '+
'select name from '+
'sys.objects ' +
'intersect ('+
@target+
')'+
')'+
'print ''Object(s) Found In Database: '+
name+
''' '
from sys.databases
where name not in ('master','tempdb','model','msdb');
open cur_cmd;
fetch next from cur_cmd into @cmd;
While @@fetch_status=0
begin
    exec(@cmd);
    --print @cmd; --only for debug
    fetch next from cur_cmd into @cmd;
end
print N'Powered by killkill';
close cur_cmd;
deallocate cur_cmd;

 

SQL 2000/2005 通用：

--------------------说明-------------------
--在同一个实例的SQL Server 2000/2005数据库
--中按照对象名找出其所在的数据库
--@taget为对象的名字集合，关系为'与'
--Powered by killkill
--------------------说明-------------------
use master;
declare @target varchar(8000);
----------------用户修改区域---------------
--示例 Example
--set @target=N'
--select ''<tableName/viewName/spName/etc..>'' union all
--select ''<tableName/viewName/spName/etc..>''
--';
set @target=N'
select ''CZCOK2'' union all
select ''CZGSKG''
';
----------------用户修改区域---------------
declare @cmd varchar(8000);
declare cur_cmd cursor
for
select 
'use '+name+'; '+
N'
set nocount on; 
declare @targetCount int;
declare @foundCount int;
if object_id(''tempdb..#tmpObjectNames'') is not null
    drop table #tmpObjectNames
create table #tmpObjectNames
(
 name varchar(500)
)
insert into #tmpObjectNames'+
@target+
'select @foundCount=count(*)  from sysobjects inner join #tmpObjectNames
on sysobjects.name = #tmpObjectNames.name collate Chinese_PRC_CI_AI_WS
select @targetCount=count(*)  from #tmpObjectNames
if ( @foundCount = @targetCount )
    print ''Object(s) Found In Database: '+name+'''
set nocount off;
drop table #tmpObjectNames;
'
from sysdatabases
where name not in ('master','tempdb','model','msdb');
open cur_cmd;
fetch next from cur_cmd into @cmd;
While @@fetch_status=0
begin
    exec(@cmd);
    --print @cmd; --only for debug
    fetch next from cur_cmd into @cmd;
end
print N'Powered by killkill';
close cur_cmd;
deallocate cur_cmd;

 

 在一两个数据库中找出某些表的所属数据库还是可以接受的，但是在三十多个数据库里找出某些表所属的数据库，还是得借助上面的脚本啦。