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

在使用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;

 

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