18

Can anyone advise on a way to list all stored procedures along with their schema names in a database? Thanks!

ROMANIA_engineer
  • 54,432
  • 29
  • 203
  • 199
daniness
  • 363
  • 1
  • 4
  • 21
  • 1
    try `select * from DatabaseName.information_schema.routines where routine_type = 'PROCEDURE'` – aserwin Sep 27 '12 at 13:48
  • Thanks, aserwin and marc_s. How do I know this will list ALL stored procedures for this database b/c I ran this and got 164 rows, compared to 1,510 records when I ran another query, which I didn't save, so I'm not sure what it was :-(. – daniness Oct 05 '12 at 16:47

7 Answers7

37
SELECT [schema] = OBJECT_SCHEMA_NAME([object_id]),
  name
FROM sys.procedures;

or

SELECT [schema] = SCHEMA_NAME([schema_id]),
  name
FROM sys.procedures;

For a specific database, you can just change the context to that database first, or change Marc's query slightly (my queries are no good in this case because they rely on functions that are context-sensitive):

SELECT 
    SchemaName = s.name,
    ProcedureName = pr.name 
FROM 
    databasename.sys.procedures pr
INNER JOIN 
    databasename.sys.schemas s ON pr.schema_id = s.schema_id;

If you want to do this for all databases:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'
  UNION ALL SELECT db = N''' + name + ''', 
    s.name COLLATE Latin1_General_CI_AI,
    o.name COLLATE Latin1_General_CI_AI
  FROM ' + QUOTENAME(name) + '.sys.procedures AS o
  INNER JOIN ' + QUOTENAME(name) + '.sys.schemas AS s
  ON o.[schema_id] = s.[schema_id]'
FROM sys.databases
-- WHERE ... -- probably don't need system databases at least

SELECT @sql = STUFF(@sql, 1, 18, '') 
  -- you may have to adjust  ^^ 18 due to copy/paste, cr/lf, tabs etc 
  + ' ORDER BY by db, s.name, o.name';

EXEC sp_executesql @sql;

The collate clauses are necessary in case you have databases with different collations.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • For SQL 2014 you can just skip `databasename` and use `use databasename`. – Michał Woliński Sep 11 '18 at 11:09
  • @MichałWoliński That doesn't work if you need to union a query across multiple databases (you can't say `USE` in the middle of a query). A database prefix on an object name does have a purpose. – Aaron Bertrand Sep 11 '18 at 11:24
  • I know, but question was for procedures in database, not multiple databases. Without database prefix 1st snippet will be easier to use. For multiple databases there is 2nd snippet, isn't it? – Michał Woliński Sep 11 '18 at 11:46
  • @MichałWoliński If you're just talking about the procedures in the database you're already in, it doesn't really matter; neither `USE` nor a database prefix are necessary. – Aaron Bertrand Sep 11 '18 at 12:13
13

Try this:

SELECT 
    SchemaName = s.name,
    ProcedureName = pr.name 
FROM 
    sys.procedures pr
INNER JOIN 
    sys.schemas s ON pr.schema_id = s.schema_id

This should list all stored procedures and their schema name as a result set.

Both views - sys.procedures and sys.schemas - have quite a few more attributes - check them out, if you need them, include them in your query.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Thanks for your suggestion. How would I use this to list all stored procedures and their schemas for a specific database? – daniness Sep 27 '12 at 14:22
  • @daniness: go to that database (`use (yourdatabase)`) and then execute this snippet of code. It works only against the database you're currently in. – marc_s Sep 27 '12 at 15:21
2

If you want to search for procs with required schema name you can use this query:

SELECT 
    SchemaName = s.name,
    ProcedureName = pr.name 
FROM 
    sys.procedures pr
INNER JOIN 
    sys.schemas s 
ON pr.schema_id = s.schema_id
WHERE s.name = 'YOUR_SCHEMA_NAME'
ORDER BY SchemaName;
Siarhei Kavaleuski
  • 1,450
  • 14
  • 15
1

this may help You..

SELECT * FROM sys.procedures;

SonalPM
  • 1,317
  • 8
  • 17
1

You can use Script Generator to get them. In the left pan right click on the database for which you want to get Stored Procedures, Tasks->Generate Scripts Click Next and choose Select Specific Database Objects and select Stored Procedures and click on next, there you can customize as you need and generate the scripts.

Sravan U
  • 67
  • 1
  • 7
0

Try this:

execute [sys].[sp_stored_procedures]

Or try this and also get all parameters:

execute [sys].[sp_sproc_columns]

Ok...you'll have to loop through all DB catalog names with this, but...

Robert Achmann
  • 1,986
  • 3
  • 40
  • 66
0
SELECT name,crdate  FROM dbo.sysobjects WHERE (type = 'P')  order by name 

SELECT [schema] = OBJECT_SCHEMA_NAME([object_id]),name FROM sys.procedures;

select OBJECT_SCHEMA_NAME([object_id]) as 'SchemaName',name as 'SP Name ' ,

create_date,modify_date FROM sys.procedures order by OBJECT_SCHEMA_NAME([object_id]), name
SonalPM
  • 1,317
  • 8
  • 17
Krushna
  • 1