Find an object in SQL Server cross-database

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

There is a schema called INFORMATION SCHEMA schema which contains a set of views on tables from the SYS schema that you can query to get what you want  A major upside of the INFORMATION SCHEMA is that the object names are very query friendly and user readable  The downside of the INFORMATION SCHEMA is that you have to write one query for each type of object  The Sys schema may seem a little cryptic initially  but it has all the same information  and more  in a single spot  You d start with a table called SysObjects  each database has one  that has the names of all objects and their types  One could 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 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  If you only want to search specific databases  use the  quot USE DBName quot  and then search command  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  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  gt 0 AND   CHARINDEX    notcontain   sysobjects name  0 or  CHARINDEX    notcontain   sysobjects name  lt  gt 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  lt    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

User · Answer

----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

User · Answer

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

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

Examples related to sql-server

Examples related to sql-server-2005