列出所有具有模式名称的存储过程 [英] List all stored procedures with schema name

问题描述

有人可以建议一种在数据库中列出所有存储过程及其模式名称的方法吗？谢谢！

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

推荐答案

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

或

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

对于特定数据库，您可以先将上下文更改为该数据库，或者稍微更改Marc的查询（我的查询在这种情况下不好，因为它们依赖于上下文相关的函数）：

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;

如果您的数据库具有不同的排序规则，则必须使用collat​​e子句。

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

这篇关于列出所有具有模式名称的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！