There can be possibility that you might need to know list all stored procedures in all databases of your SQL server system, so in this article, I have provided simple query to list all stored procedures of all database.
Script to list all stored procedure in SQl server
Here is the query which you can execute to get all stored procedures of all database in SQL server:
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
In the above query, we are creating a new table "SPs", where we will store "database name as db_name, "Stored procedure name as name" and it's "object_id".
Once we create the above table, we are executing "
SELECT from sys.procedures" for each database, loading the data into a temp table (#SPs).
sys.procedures lists out all of the stored procedures in the database and
sp_msforeachdb will run the code on each database (use ? for the database name in the code).
When I executed the above query in my local SQL Server, I got the following output:
If you don't want to use "
sp_msforeachdb ", you use the below simple query to get stored procedures list
SELECT name, type FROM dbo.sysobjects WHERE (type = 'P') -- P = stored procedures
Query to get stored procedure list for single database
You can also get list of all stored procedures from specific database, using query as below
SELECT * FROM AdventureWorks2012.sys.procedures;
Where "AdventureWorks2012" = database name.
Sample output, when executed above query
If you want to list all procedures with schema name
SELECT [schema] = OBJECT_SCHEMA_NAME([object_id]), name FROM sys.procedures;
You can also use below query if you are using older version of database like SQL Server 2005
select * from sys.procedures where is_ms_shipped = 0
That's it, hope it helps.
You may also like to read:
Stored procedure in sql server (With Example)
Understanding SQL server switch case (With Example)
How to back up SQL Server database ? (Various Ways explained)