How to find the SQL Server databases that contain a certain table or stored procedure (T-SQL)

Introduction

This T-SQL script will demonstrate how to find the SQL Server databases that contain a certain table or stored procedure.  

Scenarios

Some people want to know if a table or stored procedure exists in databases or not and which database the table/ stored procedure is contained. So we provide this script to find out the result.

 

Script

You can use this script in this way:
1. Open SQL Server Management Studio (SSMS) and connect to SQL Server.
2. Copy the code from SearchTableOrStoredProcedure.sql, paste it in a new query and run the script.
3. Execute the stored procedure with the object you want to search
For example: EXEC SearchTableOrSP test

After the script finishes running, we’ll get the following figure:

Here are some code snippet for your reference: 

CREATE PROCEDURE SearchTableOrSP
(
    @tablename sysname 
)
AS
BEGIN
DECLARE @SearchTable TABLE(DatabaseName sysname,SchemaName sysname,[Type] sysname,TableOrStoredProcedureName sysname) 
DECLARE @cmd nvarchar(500)

SET @cmd = ' USE [?]
select ''?'' AS DBName, SCHEMA_NAME(schema_id) AS SchemaName,type_desc AS Type,name FROM sys.objects WHERE type IN (''P'',''U'') AND is_ms_shipped = 0
and name = '''+ LTRIM(RTRIM(@tablename)) +''''

INSERT INTO @SearchTable
EXEC SP_MSFOREACHDB @cmd

SELECT * FROM @SearchTable
END

CREATEPROCEDURESearchTableOrSP 
( 
    @tablenamesysname  
) 
ASBEGINDECLARE@SearchTableTABLE(DatabaseNamesysname,SchemaNamesysname,[Type] sysname,TableOrStoredProcedureNamesysname)  
DECLARE@cmdnvarchar(500) 
 
SET@cmd = ' USE [?] 
select''?'' AS DBName, SCHEMA_NAME(schema_id) AS SchemaName,type_desc AS Type,name FROM sys.objects WHERE type IN (''P'',''U'') ANDis_ms_shipped = 0andname = '''+ LTRIM(RTRIM(@tablename)) +'''' 
 
INSERTINTO@SearchTableEXECSP_MSFOREACHDB@cmdSELECT * FROM@SearchTableEND

Prerequisites

SQL Server 2005 or higher version