List all stored procedures with schema name

Question

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

Answer 1

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.