How do I list all non-system stored procedures?

Question

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. 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).

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?

---

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

Answer 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).

UPDATED. Initially missed your point about is_ms_shipped.

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

 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.

Answer 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

Answer 3

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

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')

Answer 4

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.

UPDATE - 2018-11-28

Also works on SQL Server 2014

Answer 5

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.
• 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.
• launguage pseudo-procedures. These are T-SQL statements desquised as procedures and you won't find them anywhere.

Answer 6

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