What’s the solution for Script to find the list of stored procedures in all databases?

317    Asked by bruce_8968 in Salesforce , Asked on Jul 12, 2021

I need to pull out the list of stored procedures which are available in my instance. I used the following T-SQL statement to get the stored procedures in a given database. select * from MyDatabase.information_schema.routines where routine_type = 'Procedure' Is there any script to obtain all the stored procedures or to check the database name of the stored procedure by using the stored procedure name?

Answered by Carl Paige

You can use the following to get rid of sql server search stored procedures:

  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

The code above runs a USE and then a SELECT from sys.procedures for each database, loading the data into a temp table. sys.procedures lists out all of the stored procedures in the database and sp_msforeachdb will run the code on each database (use a ? for the database name in the code). Once the code is run you can query the temp table to get the consolidated list. sp_msforeachdb is known to have issues so you may want to use Aaron Bertrand's improved version located here.Hope this helps!



Your Answer

Interviews

Parent Categories