What is Script to find the list of stored procedures in all databases?

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 #StoredProcedures

Explanation

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.