Как перечислить все несистемные хранимые процедуры?

Я хочу создать запрос для списка всех пользовательских хранимых процедур, за исключением тех, которые являются системно хранимыми процедурами, учитывая, что:

  • Проверка имени типа "sp_" не работает, потому что есть хранимые пользователем процедуры, начинающиеся с "sp_".
  • Проверка свойства is_ms_shipped не работает, потому что существуют системные хранимые процедуры, у которых есть этот флаг = 0, например: sp_alterdiagram (это не MSShipped, но отображается в System Stored Procedures в SQL Server Management Studio).

Должно быть свойство или флаг где-то, так как вы можете увидеть "хранимые процедуры системы" в отдельной папке в SQL 2005. Кто-нибудь знает?

Изменить: комбинация предложений ниже работала для меня:

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

Ответы

Ответ 1

Вы должны использовать что-то вроде этого:

select * from sys.procedures where is_ms_shipped = 0

Как вы могли догадаться, ключ находится в атрибуте is_ms_shipped (он также существует в представлении sys.objects).

ОБНОВЛЕНО. Первоначально пропустил ваш вопрос о is_ms_shipped.

Это код (условие), которое 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

Здесь sp ссылается на системный вид sys.all_objects.

Ответ 2

используя первый ответ выше, я написал следующее, которое хорошо работает для моих целей:

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

Я просто брошу в "улучшенную" версию 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

Существует три типа "системных" процедур:

  • Истинные процедуры SQL, те, что содержатся в схеме "sys", будут найдены в виде обычных процедур в mssqlsystemresource.
  • Обычные пользовательские процедуры, установленные различными компонентами. Это такие, как процедуры репликации, сбор данных, отслеживание изменений, декларативная система управления и другие. Они не являются системой вообще, они живут в схеме "dbo" и просто продаются как "система". Некоторые могут быть идентифицированы флагом IsMSShipped, но не все.
  • псевдо-процедуры launguage. Это инструкции T-SQL, описанные как процедуры, и вы нигде не найдете их.

Ответ 5

попробуйте это

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

Если по какой-то причине у вас были несистемные хранимые процедуры в основной базе данных, вы могли бы использовать запрос (это отфильтрует хранимые процедуры системы MOST:

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

вы увидите больше информации в следующем ответе

Запрос, возвращающий список всех хранимых процедур

Ответ 6

Вот что я сделал на решениях выше:

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

Этот единственный запрос работает на SQL Server 2008, но не проверен на другие версии.