Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Зависимости хранимых процедур  [new]
Tketano
Member

Откуда:
Сообщений: 98
Всем привет!

Возникла следующая проблема - необходимо для для различных хранимых процедур (одна бд и схема) получить список других ХП, от которых она зависит (вызывает). Версия SQL Server: 8.0 и выше. В Management Studio есть в контекстном меню по ХП пункт "просмотреть зависимости" - то что надо. Но как сформировать такой запрос? Профайлер на компьютере нет и проверить негде, как формируется данный запрос.

Заранее спасибо!
20 дек 11, 16:48    [11798274]     Ответить | Цитировать Сообщить модератору
 Re: Зависимости хранимых процедур  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
sp_depends [ @objname = ] '<object>'

<object> ::=
{
[ database_name. [ schema_name ] . | schema_name.
object_name
}
20 дек 11, 16:52    [11798316]     Ответить | Цитировать Сообщить модератору
 Re: Зависимости хранимых процедур  [new]
step_ks
Member

Откуда:
Сообщений: 936
если и для 8.0, то sysdepends, sp_depends.
Только не факт, что увидите все зависимости, что есть на самом деле.
20 дек 11, 16:53    [11798328]     Ответить | Цитировать Сообщить модератору
 Re: Зависимости хранимых процедур  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
В общем случае - только через парсинг кода. В "просмотреть зависимости" эти зависимости будут не всегда.

И, кстати, это тоже зависимость?
set @sql = 'some_proc'
exec @sql
20 дек 11, 16:53    [11798337]     Ответить | Цитировать Сообщить модератору
 Re: Зависимости хранимых процедур  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
Гавриленко Сергей Алексеевич
В общем случае - только через парсинг кода. В "просмотреть зависимости" эти зависимости будут не всегда.
В 2008 и выше с этим проще:
use tempdb;
go

create procedure dbo.Proc1
as
begin
 exec dbo.Proc2;
 return 0;
end;
go

create procedure dbo.Proc2
as
begin
 return 0;
end;
go

exec sp_depends 'dbo.Proc1';

select
 object_schema_name(referenced_id), object_name(referenced_id)
from
 sys.sql_expression_dependencies
where
 referencing_id = object_id('dbo.Proc1') and
 referencing_class = 1;
go

drop procedure dbo.Proc1;
drop procedure dbo.Proc2;
go
20 дек 11, 17:07    [11798462]     Ответить | Цитировать Сообщить модератору
 Re: Зависимости хранимых процедур  [new]
Tketano
Member

Откуда:
Сообщений: 98
Гавриленко Сергей Алексеевич,
нет, это не зависимость =) речь только о уже созданных хранимых процедурах.
20 дек 11, 17:24    [11798621]     Ответить | Цитировать Сообщить модератору
 Re: Зависимости хранимых процедур  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Tketano
Гавриленко Сергей Алексеевич,
нет, это не зависимость =) речь только о уже созданных хранимых процедурах.
А про создание Вам и не говорили...
20 дек 11, 17:27    [11798645]     Ответить | Цитировать Сообщить модератору
 Re: Зависимости хранимых процедур  [new]
DmitryZ78
Member

Откуда: Каменск-Уральский
Сообщений: 87
Tketano,

declare @proc_name varchar(50)
set @proc_name = 'some_proc'

select distinct o.[name]
  from sysobjects o 
    join syscomments c on c.id = o.id
  where c.text like '%' + @proc_name + '%'
20 дек 11, 17:54    [11798908]     Ответить | Цитировать Сообщить модератору
 Re: Зависимости хранимых процедур  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034
DmitryZ78
Tketano,

declare @proc_name varchar(50)
set @proc_name = 'some_proc'

select distinct o.[name]
  from sysobjects o 
    join syscomments c on c.id = o.id
  where c.text like '%' + @proc_name + '%'

text = nvarchar(4000)

сорцы могут хранится порезанными на блоки.
20 дек 11, 18:42    [11799221]     Ответить | Цитировать Сообщить модератору
 Re: Зависимости хранимых процедур  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034
можно взять сборку от Denali и после нехитрых манипуляций получить приложение, которое получается такие зависимости.
20 дек 11, 18:43    [11799229]     Ответить | Цитировать Сообщить модератору
 Re: Зависимости хранимых процедур  [new]
Tketano
Member

Откуда:
Сообщений: 98
Хм, спасибо всем! В принципе, можно использовать любой SQL Server от версии 2000 и выше. Т.о. можно чтобы скрип работал только для 2008 сервере. Тогда вопрос - какой вариант лучше и надежнее? И еще я не понял, почему там могут быть не все зависимости?
А парсинг кода это вы имеете в виду искать строки с EXEC\EXECUTE в них и выдергивать название ХП?
21 дек 11, 00:49    [11800243]     Ответить | Цитировать Сообщить модератору
 Re: Зависимости хранимых процедур  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034
Tketano
Хм, спасибо всем! В принципе, можно использовать любой SQL Server от версии 2000 и выше. Т.о. можно чтобы скрип работал только для 2008 сервере. Тогда вопрос - какой вариант лучше и надежнее? И еще я не понял, почему там могут быть не все зависимости?
А парсинг кода это вы имеете в виду искать строки с EXEC\EXECUTE в них и выдергивать название ХП?

Для 2000 - точно не все зависимости - специфика такая.
А парсинг - это синтаксический разбор батча на элементы с целью поиска стейтмента EXEC и дальнейшего выдирания из него имени процедуры.
21 дек 11, 00:59    [11800262]     Ответить | Цитировать Сообщить модератору
 Re: Зависимости хранимых процедур  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034
Да. надёжнее всего - парсинг.
21 дек 11, 00:59    [11800263]     Ответить | Цитировать Сообщить модератору
 Re: Зависимости хранимых процедур  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
парсинг
9171209
Используя SqlScript моно очень простым запросом выкусить явные зависимости.
21 дек 11, 01:22    [11800298]     Ответить | Цитировать Сообщить модератору
 Re: Зависимости хранимых процедур  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
Повторюсь: в 2008 и выше не нужно ничего парсить. Есть sys.sql_expression_dependencies
sys.sql_expression_dependencies
Содержит по одной строке для каждой именованной зависимости определяемой пользователем сущности в текущей базе данных. Зависимость между двумя сущностями создается, когда имя некоторой сущности, называемой упоминаемой, встречается в составе постоянного выражения языка SQL другой сущности, называемой ссылающейся.
21 дек 11, 10:01    [11800920]     Ответить | Цитировать Сообщить модератору
 Re: Зависимости хранимых процедур  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
locky
А парсинг - это синтаксический разбор батча на элементы с целью поиска стейтмента EXEC и дальнейшего выдирания из него имени процедуры.
И как же "выдрать" имя процедуры, например, вот из этого скрипта:
DECLARE @s1 NVARCHAR,@s2 NVARCHAR,@s3 NVARCHAR(4),@s4 NVARCHAR(10), @S sysname;
SET @s1=N's';
SELECT @s2=SUBSTRING(name,21,1) FROM master.dbo.spt_values WHERE type='O9T' AND name LIKE N'X :%';
SET @s3=N'_'+NCHAR(104)+N'e';
SET @s4=N'lp';
SET @S=@s1+@s2+@s3+@s4;
EXECUTE @S;
?
Tketano
И еще я не понял, почему там могут быть не все зависимости?
Можно создать процедуру, из которой вызывается процедура, которой ещё нет.
А потом создать процедуру, вызываемую из первой.
Зависимости не будет.
Есть ещё динамический SQL, который не анализируется на предмет существования зависимостей объектов.
21 дек 11, 10:41    [11801143]     Ответить | Цитировать Сообщить модератору
 Re: Зависимости хранимых процедур  [new]
Tketano
Member

Откуда:
Сообщений: 98
invm, спасибо за помощь! ) Вроде как проверил на парочке ХП - отлично работает =)

SELECT referenced_entity_name
FROM sys.dm_sql_referenced_entities ('dbo.Proc1', 'object')
WHERE is_caller_dependent = 1
21 дек 11, 12:10    [11801827]     Ответить | Цитировать Сообщить модератору
 Re: Зависимости хранимых процедур  [new]
Tketano
Member

Откуда:
Сообщений: 98
Блин, че то ничего не работает)

SELECT *
FROM sys.dm_sql_referenced_entities ('[dbo].[Proc1]', 'OBJECT')


Возвращается список всех объектов на которые ссылается данная ХП. Как из них отсечь все кроме ХП?
21 дек 11, 12:30    [11802007]     Ответить | Цитировать Сообщить модератору
 Re: Зависимости хранимых процедур  [new]
Tketano
Member

Откуда:
Сообщений: 98
Все, разобрался, в итоге получилось два равносильных варианта:

1.

SELECT referenced_entity_name
FROM sys.sql_expression_dependencies 
WHERE referencing_id = OBJECT_ID('[dbo].[Proc1]') and OBJECTPROPERTY(OBJECT_ID('[dbo].[' + referenced_entity_name + ']'), N'IsProcedure') = 1


2.

SELECT referenced_entity_name
FROM sys.dm_sql_referenced_entities ('[dbo].[Proc1]', 'OBJECT')
WHERE OBJECTPROPERTY(OBJECT_ID('[dbo].[' + referenced_entity_name + ']'), N'IsProcedure') = 1
21 дек 11, 12:46    [11802163]     Ответить | Цитировать Сообщить модератору
 Re: Зависимости хранимых процедур  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034
iap
И как же "выдрать" имя процедуры, например, вот из этого скрипта:

У меня имен хандлеров вообще в метаданных системы хранятся, как это выдрать из скрипта? :)
Никак, разумеется.
Но парсинг всё-таки надёжнее, чем всякие там like и прочее.
21 дек 11, 14:42    [11803447]     Ответить | Цитировать Сообщить модератору
 Re: Зависимости хранимых процедур  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
invm
sys.sql_expression_dependencies, sys.dm_sql_referenced_entities
Но, например, получить сквозное сопоставление колонок представления и колонок используемых объектов (мапинг) нельзя.
Это привилегия только оптимизатора / парсига.
23 дек 11, 12:13    [11813954]     Ответить | Цитировать Сообщить модератору
 Re: Зависимости хранимых процедур  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
Mnior
invm
sys.sql_expression_dependencies, sys.dm_sql_referenced_entities
Но, например, получить сквозное сопоставление колонок представления и колонок используемых объектов (мапинг) нельзя.
Это привилегия только оптимизатора / парсига.
Ну метаданные под такие зависимости уже есть, так что будем надеятся, что когда-нибудь такую фичу реализуют.
24 дек 11, 12:04    [11819127]     Ответить | Цитировать Сообщить модератору
 Re: Зависимости хранимых процедур  [new]
Tketano
Member

Откуда:
Сообщений: 98
Данный способ позволяет отловить мне необходимые зависимости между ХП, функциями, представлениями и таблицами...

select	referenced_entity_name
from	sys.dm_sql_referenced_entities ('dbo.Table1', 'OBJECT')


Однако, не получается установить зависимость таблицы от других таблиц (речь о FK). Как определить данные зависимости?
27 дек 11, 14:24    [11833117]     Ответить | Цитировать Сообщить модератору
 Re: Зависимости хранимых процедур  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
Tketano
Однако, не получается установить зависимость таблицы от других таблиц (речь о FK). Как определить данные зависимости?
sys.foreign_keys, sys.foreign_key_columns
27 дек 11, 15:06    [11833492]     Ответить | Цитировать Сообщить модератору
 Re: Зависимости хранимых процедур  [new]
Tketano
Member

Откуда:
Сообщений: 98
invm,

Т.е. так:

select	so.[name]
from	sys.foreign_keys as fk
	inner join sys.objects as so
		on fk.[referenced_object_id] = so.[object_id]
where	fk.[parent_object_id] = object_id('dbo.Table1')


?

Кстати, нашел еще одну интересную системную ХП, которая помогает для определения данных вещей - [sys].[sp_MSdependencies].
27 дек 11, 15:41    [11833786]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить