CODEWITHSUNDEEP
sqlsql-server-2005
bubbassauro
bubbassauro

Reputation: 4279

How do I list all non-system stored procedures?

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

Upvotes: 21

Views: 20254

Answers (7)

Mehdi Hassani Goodarzi
Mehdi Hassani Goodarzi

Reputation: 157

This solution help for extract All Procedures, Scalar User Define Function and Table Value Function that are not system with important Data with Very simple query plan

SELECT 
        T1.object_id,
        CONVERT(VARCHAR(200),LEFT(T1.[name],200)) AS ObjectName,
        CONVERT(VARCHAR(10),T1.[type]) AS ObjectType,
        T1.create_date ,
        T1.modify_date,
        T2.name,
        (SELECT TOP(1) CAST(value AS VARCHAR(250)) FROM sys.extended_properties WHERE major_id=T1.object_id AND minor_id=0) AS ObjectDescription,
        T3.definition AS ObjectDefinition
    FROM 
        sys.objects AS T1  INNER JOIN 
        sys.schemas AS T2 ON  T1.schema_id=T2.schema_id LEFT JOIN 
        sys.sql_modules T3 ON  T3.object_id = T1.object_id 
    WHERE 
        T1.type IN ('FN','TF','P') 
        AND  T3.definition IS NOT NULL
    ORDER BY 
        T1.name

Upvotes: -1

Mark
Mark

Reputation: 8431

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

Upvotes: 3

Abacus
Abacus

Reputation: 2109

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

Upvotes: 4

Jeffrey Bradley
Jeffrey Bradley

Reputation: 696

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

Upvotes: 4

AlexS
AlexS

Reputation: 2386

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.

Upvotes: 18

Remus Rusanu
Remus Rusanu

Reputation: 294287

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.

Upvotes: 2

Hiyasat
Hiyasat

Reputation: 8926

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

Upvotes: 1

Related Questions