CODEWITHSUNDEEP
sqlsql-serverstored-proceduresssms
Barney Chambers
Barney Chambers

Reputation: 2783

SQL Server Management Studio - find stored procedure by name over multiple databases

I have recently joined a software project that has approximately 20-40 databases.

Each database has at least 200 stored procedures, some of them have many more, so it is very slow for me to search for a particular procedure manually.

I know that there is a stored procedure which I need to look at to fix a bug, somewhere in the entire project, it is called XYZ_procedure

How do I search for this procedure over all of my databases in SQL Server Management Studio?

Upvotes: 4

Views: 2838

Answers (5)

Sarath Rachuri
Sarath Rachuri

Reputation: 2116

I found the query on a similar question. The answer suggests using the following SQL query,

SELECT [name] AS DatabaseName
FROM sys.databases
WHERE OBJECT_ID(QUOTENAME(name) + '.dbo.ProcedureNameHere', 'P') IS NOT NULL

You can find the original answer and more details at this link

Upvotes: 0

Denis Rubashkin
Denis Rubashkin

Reputation: 2191

I think the simplest way is to use the undocumented stored procedure sp_MSForeachdb which executes a command for each database:

EXEC sp_MSforeachdb 
'
USE ?
IF EXISTS (
    SELECT 1 
    FROM sys.objects
    WHERE name = ''XYZ_procedure''
    )
    SELECT DB_NAME();
'

Upvotes: 0

Ajinkya Jeurkar
Ajinkya Jeurkar

Reputation: 78

If I am getting it right. You need a way to filter the procedures by name in SSMS. You can refer the following link :

Find Using Filter Settings In Object Explorer

Upvotes: 1

marc_s
marc_s

Reputation: 755321

If you need to find database objects (e.g. tables, columns, triggers) by name - have a look at the FREE Red-Gate tool called SQL Search which does this - it searches your entire database for any kind of string(s).

enter image description here

enter image description here

It's a great must-have tool for any DBA or database developer - did I already mention it's absolutely FREE to use for any kind of use??

Upvotes: 1

Alfaiz Ahmed
Alfaiz Ahmed

Reputation: 1728

You can use dynamic SQL to check procedure over all of databases in SQL Server Management Studio

USE MASTER
GO
BEGIN TRAN

DECLARE @strt INT,@End INT,@Database NVARCHAR(50)

SELECT * INTO #T FROM Sys.databases WITH(NOLOCK) WHERE database_id>4 
ORDER BY 1

SELECT ROW_NUMBER ()OVER (ORDER BY database_Id)Db_Id,* INTO #TT FROM #T
SET @strt=1
SELECT @End=Max(Db_ID)FROM #tt

WHILE @strt<=@END
     BEGIN
         DECLARE @string NVARCHAR(MAX)
         SELECT @Database=NAME FROM #TT WHERE Db_ID=@strt

        Set @string='  Select '''+@Database+'''db_Name,* from '+@Database+'.sys.objects 
          WHERE Name=''XYZ_procedure'''

          SET @strt=@strt+1
          PRINT @string
          EXEC(@string)
     END

ROLLBACK TRAN

Upvotes: 1

Related Questions