如何列出所有非系统存储过程?

I want to create a query to list of all user defined stored procedures, excluding the ones that are system stored procedures, considering that:

我想创建一个查询，列出所有用户定义的存储过程，不包括系统存储过程，因为:

1. Checking the name like "sp_" doesn't work because there are user stored procedures that start with "sp_".
2. 像“sp_”这样的名称检查不起作用，因为有以“sp_”开头的用户存储过程。
3. Checking the property is_ms_shipped doesn't work because there are system stored procedures that have that flag = 0, for example: sp_alterdiagram (it is not MSShipped but appears under System Stored Procedures in SQL Server Management Studio).
4. 检查is_ms_shipped属性无效，因为有一些系统存储过程具有该标志= 0，例如:sp_alterdiagram(它不是MSShipped，而是在SQL Server Management Studio的系统存储过程中出现)。

There must be a property, or a flag somewhere since you can see the "System Stored Procedures" in a separate folder in SQL 2005. Does anyone know?

必须有一个属性或标志，因为您可以在SQL 2005中单独的文件夹中看到“系统存储过程”。有人知道吗?

---

Edit: A combination of the suggestions below worked for me:

编辑:下面的建议对我起了作用:

select *
from 
    sys.objects             O LEFT OUTER JOIN
    sys.extended_properties E ON O.object_id = E.major_id
WHERE
    O.name IS NOT NULL
    AND ISNULL(O.is_ms_shipped, 0) = 0
    AND ISNULL(E.name, '') <> 'microsoft_database_tools_support'
    AND O.type_desc = 'SQL_STORED_PROCEDURE'
ORDER BY O.name

6 个解决方案

#1

You should use something like this:

你应该用这样的东西:

select * from sys.procedures where is_ms_shipped = 0

As you could guess, the key is in is_ms_shipped attribute (it exists in sys.objects view as well).

您可以猜到，键在is_ms_shipped属性中(它存在于sys中。对象视图)。

UPDATED. Initially missed your point about is_ms_shipped.

更新。最初我没有理解您关于is_ms_shipped的观点。

This is the code (condition) that Management Studio actually uses to retrieve a list of 'system stored procedures'

这是Management Studio实际用于检索“系统存储过程”列表的代码(条件)

 CAST(
 case 
 when sp.is_ms_shipped = 1 then 1
 when (
    select 
        major_id 
    from 
        sys.extended_properties 
    where 
        major_id = sp.object_id and 
        minor_id = 0 and 
        class = 1 and 
        name = N''microsoft_database_tools_support'') 
    is not null then 1
 else 0
 end AS BIT) = 1

Here sp refers to sys.all_objects system view.

这里sp指的是sys。all_objects系统视图。

#2

using the first answer above, I wrote the following which works well for my uses:

使用上面的第一个答案，我写了下面的答案，这对我的用途很有用:

select
        *
    from
        INFORMATION_SCHEMA.ROUTINES as ISR
    where
        ISR.ROUTINE_TYPE = 'PROCEDURE' and
        ObjectProperty (Object_Id (ISR.ROUTINE_NAME), 'IsMSShipped') = 0 and
        (
            select 
                major_id 
            from 
                sys.extended_properties 
            where 
                major_id = object_id(ISR.ROUTINE_NAME) and 
                minor_id = 0 and 
                class = 1 and 
                name = N'microsoft_database_tools_support'
        ) is null
    order by
        ISR.ROUTINE_CATALOG,
        ISR.ROUTINE_SCHEMA,
        ISR.ROUTINE_NAME

#3

I'll just toss in my "improved" version of SQL (realizing that formatting is a matter of personal preference):

我将加入我“改进”的SQL版本(认识到格式化是个人偏好的问题):

SELECT *
FROM [sys].[procedures] sp
WHERE is_ms_shipped = 0
AND NOT EXISTS (
    select ep.[major_id]
    from [sys].[extended_properties] ep
    where ep.[major_id] = sp.[object_id]
    and ep.[minor_id] = 0
    and ep.[class] = 1
    and ep.[name] = N'microsoft_database_tools_support')

#4

There are three kinds of 'system' procedures:

有三种“制度”程序:

• True SQL procedures, the ones in the 'sys' schema, will be found as ordinary procedures in mssqlsystemresource database.
• 在mssqlsystemresource数据库中，真正的SQL过程(‘sys’模式中的那些)将作为普通的过程。
• Ordinary user procedures installed by various components. These are the likes of replication procedures, data collection, change tracking, declarative managmenet framework and other. They are not system at all, they live in the 'dbo' schema and are simply marketed as 'system'. Some can be identified by the 'IsMSShipped' flag, but not all.
• 普通用户程序由各种组件安装。这些类似于复制过程、数据收集、更改跟踪、声明式managmenet框架等。它们根本不是系统，它们生活在“dbo”模式中，只是简单地以“系统”来营销。有些可以通过IsMSShipped标志识别，但不是全部。
• launguage pseudo-procedures. These are T-SQL statements desquised as procedures and you won't find them anywhere.
• launguage pseudo-procedures。这些T-SQL语句被当作过程，你在任何地方都找不到它们。

#5

try this

试试这个

select * from DatabaseName.information_schema.routines where routine_type = 'PROCEDURE'

If for some reason you had non-system stored procedures in the master database, you could use the query (this will filter out MOST system stored procedures:

如果由于某种原因，主数据库中有非系统存储过程，可以使用查询(这将过滤掉大多数系统存储过程:

select * from master.information_schema.routines where routine_type = 'PROCEDURE' and
Left(Routine_Name, 3) NOT IN ('sp_', 'xp_', 'ms_')

you see more information in the following answer

您将在以下答案中看到更多信息

Query that returns list of all Stored Procedures

返回所有存储过程的列表的查询

#6

Here's what I did base on the solutions above:

以下是我基于以上解决方案所做的:

select * from sys.procedures 
 where object_id not in(select major_id from sys.extended_properties)

This single query works on SQL Server 2008 but haven't tested to other versions.

这个查询在SQL Server 2008上运行，但还没有对其他版本进行测试。