在SQL Server(跨数据库)中找到一个对象

If I've been told a table (or proc) name, but not which connected database the object is located in, is there any simple script to search for it? Maybe search somewhere in the System Databases? (I'm using SQL Server 2005)

如果我被告知一个表(或proc)的名称，而不是该对象所在的连接数据库，那么有什么简单的脚本可以搜索它吗?也许在系统数据库的某个地方搜索?(我使用的是SQL Server 2005)

5 个解决方案

#1

There is an Information_Schema schema which is a set of views on tables from the SYS schema that you can query to get what you want.

这里有一个Information_Schema模式，它是一组从SYS模式的表上的视图，您可以通过查询来得到您想要的。

The downside of the Information_Schema is that you have to write one query for each type of object. Upside is that the Information_Schema is more friendly to read as well.

Information_Schema的缺点是必须为每种类型的对象编写一个查询。好处是，Information_Schema也更便于阅读。

The Sys schema will initially seem a little cryptic but it has the same information in a single spot.

Sys模式最初看起来有点神秘，但它在一个点上有相同的信息。

Basically, there is a table called SysObjects in each database that has the names of all objects and their types.

基本上，每个数据库中都有一个名为SysObjects的表，它具有所有对象的名称及其类型。

So, you would want to search in a database as follows:

因此，您需要在数据库中搜索如下:

Select [name] as ObjectName, Type as ObjectType
From Sys.Objects
Where 1=1
and [Name] like '%YourObjectName%'

Now, if you wanted to restrict this to only search for tables and stored procs, you would do

现在，如果你想限制它只搜索表格和存储的procs，你就会这么做。

Select [name] as ObjectName, Type as ObjectType
From Sys.Objects
Where 1=1
and [Name] like '%YourObjectName%'
and Type in ('U', 'P')

If you look up object types, you will find a whole list for views, triggers, etc.

如果您查找对象类型，您将会发现一个完整的视图列表、触发器等。

Now, if you want to search for this in each database, you will have to iterate through the databases. You can do one of the following:

现在，如果您想在每个数据库中搜索这个，就必须遍历数据库。你可以这样做:

If you want to search through each database without any clauses, then use the sp_MSforeachdb as shown in an answer here.

如果您想在每个数据库中搜索没有任何子句，那么可以使用sp_MSforeachdb作为答案。

If you only want to search specific databases, use the "USE DBName" and then search command.

如果您只想搜索特定的数据库，请使用“使用DBName”，然后搜索命令。

You will benefit greatly from having it parameterized in that case. Note that the name of the database you are searching in will have to be replaced in each query (DatabaseOne, DatabaseTwo...). Check this out:

如果在这种情况下参数化，您将受益匪浅。注意，要搜索的数据库的名称必须在每个查询中替换(DatabaseOne, DatabaseTwo…)。看看这个:

Declare @ObjectName VarChar (100)

Set @ObjectName = '%Customer%'

Select 'DatabaseOne' as DatabaseName, [name] as ObjectName, Type as ObjectType
From DatabaseOne.Sys.Objects
Where 1=1
and [Name] like @ObjectName
and Type in ('U', 'P')
UNION ALL
Select 'DatabaseTwo' as DatabaseName, [name] as ObjectName, Type as ObjectType
From DatabaseTwo.Sys.Objects
Where 1=1
and [Name] like @ObjectName
and Type in ('U', 'P')
UNION ALL
Select 'DatabaseThree' as DatabaseName, [name] as ObjectName, Type as ObjectType
From DatabaseThree.Sys.Objects
Where 1=1
and [Name] like @ObjectName
and Type in ('U', 'P')

#2

sp_MSforeachdb 'select db_name(), * From ?..sysobjects where xtype in (''U'', ''P'') And name = ''ObjectName'''

Instead of 'ObjectName' insert object you are looking for. First column will display name of database where object is located at.

而不是“ObjectName”插入对象。第一列将显示对象所在的数据库的名称。

#3

Easiest way is to hit up the information_schemas...

最简单的方法是打开information_schema…

SELECT *
FROM information_schema.Tables
WHERE [Table_Name]='????'

SELECT *
FROM information_schema.Views
WHERE [Table_Name]='????'

SELECT *
FROM information_schema.Routines
WHERE [Routine_Name]='????'

#4

You can use sp_MSforeachdb to search all databases.

您可以使用sp_MSforeachdb来搜索所有数据库。

declare @RETURN_VALUE int

声明@RETURN_VALUE int

declare @command1 nvarchar(2000)

声明@command1 nvarchar(2000)

set @command1 = "Your command goes here"

设置@command1 = "你的命令在这里"

exec @RETURN_VALUE = sp_MSforeachdb @command1 = @command1

exec @RETURN_VALUE = sp_MSforeachdb @command1 = @command1。

Raj

拉吉

#5

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


/**********************************************************************
Naziv procedure     : sp_rfv_FIND
Ime i prezime autora: Srdjan Nadrljanski
Datum kreiranja     : 13.06.2013. 
Namena              : Traži sql objekat na celom serveru
Tabele              : 
Ulazni parametri    : 
Izlazni parametri   : 
Datum zadnje izmene :  
Opis izmene         : 
exec sp_rfv_FIND 'TUN',''
**********************************************************************/
CREATE PROCEDURE [dbo].[sp_rfv_FIND] (  @SEARCHSTRING VARCHAR(255),
                                        @notcontain Varchar(255)
                                        )
AS


declare @text varchar(1500),@textinit varchar (1500)
set @textinit=
'USE @sifra

insert into ##temp2
select ''@sifra''as dbName,a.[Object Name],a.[Object Type]
from(
 SELECT DISTINCT sysobjects.name AS [Object Name]   ,
case
when sysobjects.xtype = ''C'' then ''CHECK constraint''
when sysobjects.xtype = ''D'' then ''Default or DEFAULT constraint''
when sysobjects.xtype = ''F'' then ''Foreign Key''
when sysobjects.xtype = ''FN'' then ''Scalar function''
when sysobjects.xtype = ''P'' then ''Stored Procedure''
when sysobjects.xtype = ''PK'' then ''PRIMARY KEY constraint''
when sysobjects.xtype = ''S'' then ''System table''
when sysobjects.xtype = ''TF'' then ''Function''
when sysobjects.xtype = ''TR'' then ''Trigger''
when sysobjects.xtype = ''U'' then ''User table''
when sysobjects.xtype = ''UQ'' then ''UNIQUE constraint''
when sysobjects.xtype = ''V'' then ''View''
when sysobjects.xtype = ''X'' then ''Extended stored procedure''
end as [Object Type]
FROM sysobjects
WHERE
sysobjects.type in (''C'',''D'',''F'',''FN'',''P'',''K'',''S'',''TF'',''TR'',''U'',''V'',''X'')
AND sysobjects.category = 0
AND CHARINDEX(''@SEARCHSTRING'',sysobjects.name)>0
AND ((CHARINDEX(''@notcontain'',sysobjects.name)=0 or 
CHARINDEX(''@notcontain'',sysobjects.name)<>0)) 
)a'

    set @textinit=replace(@textinit,'@SEARCHSTRING',@SEARCHSTRING)
    set @textinit=replace(@textinit,'@notcontain',@notcontain)


SELECT name AS dbName,cast(null as varchar(255)) as ObjectName,cast(null as varchar(255)) as ObjectType  
into ##temp1 
from master.dbo.sysdatabases order by name

SELECT * INTO ##temp2 FROM ##temp1 WHERE 1 = 0


declare @sifra VARCHAR(255),@suma int,@brojac int

set @suma=(select count(dbName) from ##temp1) 

DECLARE c_k CURSOR LOCAL FAST_FORWARD FOR
SELECT dbName FROM ##temp1 ORDER BY dbName DESC

OPEN c_k
FETCH NEXT FROM c_K INTO @sifra
SET @brojac = 1
WHILE (@@fetch_status = 0 ) AND (@brojac <= @suma)
BEGIN

    set @text=replace(@textinit,'@sifra',@sifra)

    exec (@text)

    SET @brojac = @brojac +1

    DELETE FROM ##temp1 WHERE dbName = @sifra

       FETCH NEXT FROM c_k INTO @sifra 
END
close c_k
DEALLOCATE c_k

select * from ##temp2
order by dbName,ObjectType
drop table ##temp2
drop table ##temp1

#1