[sql-server] Find an object in SQL Server (cross-database)

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)

----Option 2

SELECT DISTINCT
    o.name,
    o.xtype
FROM
    syscomments c
    INNER JOIN
        sysobjects o
        ON
            c.id=o.id
WHERE
    c.TEXT LIKE '%TableName%'
order by
    o.name desc,
    o.xtype desc

You can achieve this by using the following query:

EXEC sp_msforeachdb 
    'IF EXISTS
    (
        SELECT  1 
        FROM    [?].sys.objects 
        WHERE   name LIKE ''OBJECT_TO_SEARCH''
    )
    SELECT 
        ''?''       AS DB, 
        name        AS Name, 
        type_desc   AS Type 
    FROM [?].sys.objects 
    WHERE name LIKE ''OBJECT_TO_SEARCH'''

Just replace OBJECT_TO_SEARCH with the actual object name you are interested in (or part of it, surrounded with %).

More details here: https://peevsvilen.blog/2019/07/30/search-for-an-object-in-sql-server/

SELECT NAME AS ObjectName
    ,schema_name(o.schema_id) AS SchemaName, OBJECT_NAME(o.parent_object_id) as TableName
    ,type
    ,o.type_desc
FROM sys.objects o
WHERE o.is_ms_shipped = 0
    AND o.NAME LIKE '%UniqueID%'
ORDER BY o.NAME

select db_name(), * From sysobjects where xtype in ('U', 'P') And name = 'OBJECT_name'

First column will display name of database where object is located at.

You can use sp_MSforeachdb to search all databases.

declare @RETURN_VALUE int

declare @command1 nvarchar(2000)

set @command1 = "Your command goes here"

exec @RETURN_VALUE = sp_MSforeachdb @command1 = @command1

Raj

Easiest way is to hit up the information_schemas...

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

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

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

mayby one little change from the top answer, because DB_NAME() returns always content db of execution. so, for me better like below:

sp_MSforeachdb 'select DB_name(db_id(''?'')) as DB, * From ?..sysobjects where xtype in (''U'', ''P'') And name like ''[_]x[_]%'''

In my case I was looking for tables their names started with _x_

Cheers, Ondrej

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

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.