Check if procedure exists in any database SQL Server

Question

I'd like to know if there is any way to search for a procedure in all the databases of a server (there are only SQL Server databases on that server).

So far I've only found how to find a stored procedure in a certain database with Object_id(), but it would take too long for me to manually search each database by hand. I'd also like to see in which database is the stored procedure applied.

Code I've found so far:

select * 
from MyDataBase.sys.objects 
where object_id = object_id(N'MyProcedure')

You would need to use dynamic SQL and either loop through all the databases, or create a query that queries them all. — Thom A
– Thom A ♦, Commented Aug 20, 2021 at 9:41
Does this answer your question? Is it possible to search all databases in a SQL Server for a specific view? If yes how? — Thom A
– Thom A ♦, Commented Aug 20, 2021 at 9:44

gandalf · Accepted Answer · 2021-08-20 12:01:41Z

2

You can try this code

CREATE TABLE #SPs (db_name varchar(100), name varchar(100), object_id int)

EXEC sp_msforeachdb 'USE [?]; INSERT INTO #SPs select ''?'', name, object_id from sys.procedures'

SELECT * FROM #SPs

See also :https://dba.stackexchange.com/questions/130399/script-to-find-the-list-of-stored-procedures-in-all-databases

answered Aug 20, 2021 at 12:01

gandalf

512 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Check if procedure exists in any database SQL Server

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related