What is Script to find the list of stored procedures in all databases?
I want 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 is any script to obtain the all stored procedures or to check the database name of the stored procedure by using the stored procedure name? What is sql server search stored procedures?
SQL server search stored procedures A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it. You can use the following code for 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 databasename 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.
Answer (1)
To find the list of stored procedures in all databases on a SQL Server instance, you can use a combination of dynamic SQL and system catalog views. Below is a script that will achieve this:
DECLARE @DatabaseName NVARCHAR(255)
DECLARE @SQL NVARCHAR(MAX)
-- Table to store the results
CREATE TABLE #StoredProcedures (
DatabaseName NVARCHAR(255),
SchemaName NVARCHAR(255),
ProcedureName NVARCHAR(255)
)
-- Cursor to iterate through all databases
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE state_desc = 'ONLINE' -- Only look at online databases
AND database_id > 4 -- Skip system databases
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
-- Build the dynamic SQL to execute in the context of each database
SET @SQL = N'
USE [' + @DatabaseName + '];
INSERT INTO #StoredProcedures (DatabaseName, SchemaName, ProcedureName)
SELECT ''' + @DatabaseName + ''', SCHEMA_NAME(schema_id), name
FROM sys.procedures;'
-- Execute the dynamic SQL
EXEC sp_executesql @SQL
FETCH NEXT FROM db_cursor INTO @DatabaseName
END
CLOSE db_cursor
DEALLOCATE db_cursor
-- Select the results
SELECT *
FROM #StoredProcedures
-- Drop the temporary table
DROP TABLE #StoredProceduresExplanation
Temporary Table: A temporary table #StoredProcedures is created to store the results.
Cursor: A cursor db_cursor is used to iterate through all the databases in the instance that are online and are not system databases (with database_id > 4).
Dynamic SQL: For each database, dynamic SQL is constructed to switch to the database using USE [DatabaseName] and then insert the stored procedure details into the temporary table. The sys.procedures catalog view is queried to get the stored procedures for each database.
Execution of Dynamic SQL: The constructed SQL is executed using sp_executesql.
Fetching Results: After iterating through all databases, the results are selected from the temporary table.
Cleanup: The temporary table is dropped at the end.
Note
Ensure you have adequate permissions to read from all the databases and to create and drop temporary tables.
This script skips the system databases (like master, model, msdb, tempdb) by filtering out database_id > 4. Adjust this condition if you need to include them.
Use this script with caution on large instances, as it can generate a lot of queries and results.