Как я перечисляю все хранимые процедуры бессистемности?
Я хочу создать вопрос списку определенных хранимых процедур всего пользователя, исключая тех, которые являются системными хранимыми процедурами, полагая что:
- Проверка имени как «SP _» не работает, потому что есть пользовательские хранимые процедуры, которые начинаются с «SP _».
- Проверяя собственность is_ms_shipped не работает, потому что есть системные хранимые процедуры, у которых есть тот флаг = 0, например: sp_alterdiagram (это не MSShipped, но появляется в соответствии с Системными Хранимыми процедурами в Студии управления SQL-сервером).
Должна быть собственность или флаг где-нибудь, так как Вы видите «Системные Хранимые процедуры» в отдельной папке в 2005 SQL. Кто-либо знает?
Править: Комбинация предложений ниже обработанного для меня:
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
4 ответа
Вы должны использовать нечто подобное:
select * from sys.procedures where is_ms_shipped = 0
Как вы могли догадаться, ключ находится в атрибуте is_ms_shipped (он существует и в представлении sys.objects).
UPDATED. Изначально вы пропустили пункт о is_ms_shipped.
Это код (условие), который на самом деле используется в студии управления для получения списка "системных хранимых процедур"
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.
.попробуйте это
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_')
дополнительная информация приведена в следующем ответе
Существуют три вида "системных" процедур:
- Истинные SQL-процедуры, те, что в 'sys' схеме, можно найти как обычные процедуры в БД mssqlsystemresource.
- Обычные пользовательские процедуры, устанавливаемые различными компонентами. Это такие как процедуры репликации, сбора данных, отслеживания изменений, фреймворк декларативного управления и другие. Они вовсе не системные, они живут в схеме 'dbo' и просто продаются как 'system'. Некоторые могут быть идентифицированы по флагу 'IsMSShipped', но не все.
- launguage pseudo-procesures. Это T-SQL-запросы, объявленные процедурами, и вы их нигде не найдете.
Используя первый ответ выше, я написал следующее, которое хорошо подходит для моих целей:
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