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

Откуда: Москва
Сообщений: 2022
Всем привет!
На сервере есть 30+ ХП.
Хочу узнать список объектов(процедуры, функции, таблицы и вьюхи) которые необходимы для работы этих процедур.
Всё что не войдёт в этот список - будет удалено.

Может кто подсказать с запросом ?
Это можно конечно сделать в SSMS кликая на каждую ХП по отдельности, но как сделать запрос ?
8 дек 15, 11:32    [18528397]     Ответить | Цитировать Сообщить модератору
 Re: От каких объектов зависит Хранимая Процедура ?  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
Valery_B,

абсолютно все зависимости никаким запросом не получите.
Я бы не стал ничего удалять.
8 дек 15, 11:40    [18528453]     Ответить | Цитировать Сообщить модератору
 Re: От каких объектов зависит Хранимая Процедура ?  [new]
Valery_B
Member

Откуда: Москва
Сообщений: 2022
iap,

Как же это делает SSMS ?
8 дек 15, 11:42    [18528477]     Ответить | Цитировать Сообщить модератору
 Re: От каких объектов зависит Хранимая Процедура ?  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
Valery_B
iap,

Как же это делает SSMS ?
А кто сказал, что там получаются все зависимости?
Например, в процедуре - динамический SQL. Имя таблицы добавляется в строку запроса посимвольно.
Покажет SSMS зависимость процедуры от этой таблицы? Очевидно нет. А она есть!
8 дек 15, 11:45    [18528513]     Ответить | Цитировать Сообщить модератору
 Re: От каких объектов зависит Хранимая Процедура ?  [new]
Valery_B
Member

Откуда: Москва
Сообщений: 2022
Или как узнать список объектов(и их тип), которые нужны одной конкретной процедуре ?
8 дек 15, 11:46    [18528520]     Ответить | Цитировать Сообщить модератору
 Re: От каких объектов зависит Хранимая Процедура ?  [new]
dvim
Member

Откуда: Санкт Петербург
Сообщений: 680
Valery_B,

Плохо делает.

Если просто прямая зависимость, это одно.
А вот ежели нужно рекурсивно искать - то это достаточно не просто.
(Ибо хп ссылается на функцию, где используется вьюха, которой нужна некая таблица)
8 дек 15, 11:46    [18528527]     Ответить | Цитировать Сообщить модератору
 Re: От каких объектов зависит Хранимая Процедура ?  [new]
Valery_B
Member

Откуда: Москва
Сообщений: 2022
В этих процедурах нет динамического SQL
8 дек 15, 11:47    [18528536]     Ответить | Цитировать Сообщить модератору
 Re: От каких объектов зависит Хранимая Процедура ?  [new]
правильный проходящий.
Guest
Valery_B
iap,

Как же это делает SSMS ?
А ЭТО - это ЧТО? Удаление не нужных? Поиск не нужных? Определение зависимостей? Если последнее, то еще раз -
абсолютно все зависимости никаким запросом не получите.
Некоторые найти можно, но не все.
8 дек 15, 11:48    [18528544]     Ответить | Цитировать Сообщить модератору
 Re: От каких объектов зависит Хранимая Процедура ?  [new]
Valery_B
Member

Откуда: Москва
Сообщений: 2022
create procedure Test as
begin
 Insert into Table1 values(1)
 Insert into Table2 values(2)
end


В данном случае, процедура Test зависит от двух объектов - таблицы Table1 и Table2
SMSS их показывает правильно.
8 дек 15, 11:53    [18528598]     Ответить | Цитировать Сообщить модератору
 Re: От каких объектов зависит Хранимая Процедура ?  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
Ну лежат процедуры, кушать не просят.
Зачем их удалять-то?

По-хорошему надо каждую прочитать и осмыслить.
После этого решать - можно удалить или нет.
8 дек 15, 11:53    [18528601]     Ответить | Цитировать Сообщить модератору
 Re: От каких объектов зависит Хранимая Процедура ?  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
Valery_B
Хочу узнать список объектов(процедуры, функции, таблицы и вьюхи) которые необходимы для работы этих процедур.
select
 t.n, r.referenced_server_name, r.referenced_database_name, r.referenced_schema_name, r.referenced_entity_name
from
 (values ('Proc1'), ..., ('ProcN')) t(n) cross apply
 sys.dm_sql_referenced_entities(t.n, 'object') r
where
 r.referenced_minor_id = 0
order by
 t.n;

Valery_B
Всё что не войдёт в этот список - будет удалено.
Не горячитесь.
Создайте отдельную схему и переместите в нее.
8 дек 15, 11:54    [18528611]     Ответить | Цитировать Сообщить модератору
 Re: От каких объектов зависит Хранимая Процедура ?  [new]
msLex
Member

Откуда:
Сообщений: 8217
Valery_B
В этих процедурах нет динамического SQL


начните от сюда sys.sql_expression_dependencies
8 дек 15, 11:54    [18528615]     Ответить | Цитировать Сообщить модератору
 Re: От каких объектов зависит Хранимая Процедура ?  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
Valery_B
create procedure Test as
begin
 Insert into Table1 values(1)
 Insert into Table2 values(2)
end



В данном случае, процедура Test зависит от двух объектов - таблицы Table1 и Table2
SMSS их показывает правильно.
create procedure Test1 as
DECLARE @Proc sysname = N'Test2';
EXEC @Proc;
Test1 зависит от Test2?
Никакого динамического SQL!
8 дек 15, 11:59    [18528671]     Ответить | Цитировать Сообщить модератору
 Re: От каких объектов зависит Хранимая Процедура ?  [new]
Джонни Кейдж
Member

Откуда: стольный град Киев
Сообщений: 31
SELECT * FROM sys.sysdepends
8 дек 15, 18:57    [18531824]     Ответить | Цитировать Сообщить модератору
 Re: От каких объектов зависит Хранимая Процедура ?  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34621
iap
Valery_B
iap,

Как же это делает SSMS ?
А кто сказал, что там получаются все зависимости?
Например, в процедуре - динамический SQL. Имя таблицы добавляется в строку запроса посимвольно.
Покажет SSMS зависимость процедуры от этой таблицы? Очевидно нет. А она есть!



кроме этого, косвенные зависимости тоже не показываются. Например, процедуры используют таблицу А, которая ссылается по FM на таблицу B. A покажет, B нет.
8 дек 15, 19:16    [18531911]     Ответить | Цитировать Сообщить модератору
 Re: От каких объектов зависит Хранимая Процедура ?  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4271
Я обычно использую запросы к таблице sys.syscomments, которая содержит ВСЕ тексты вьюх, процедур, триггеров и т.п.
Проджийнив к ним доп. таблицы и системные вьюхи можете получить информацию где есть упоминание о том или ином объекте.
Если найду готовый скрипт, то выложу сюда пример.
9 дек 15, 10:21    [18533696]     Ответить | Цитировать Сообщить модератору
 Re: От каких объектов зависит Хранимая Процедура ?  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4271
Вот нашел
select * from sys.syscomments c
inner join sys.all_objects o on c.id = o.object_id
where c.[text] like N'%TABLE_NAME%'

Подставьте вместо TABLE_NAME - имя вашей таблицы или иного объекта БД.
9 дек 15, 10:26    [18533729]     Ответить | Цитировать Сообщить модератору
 Re: От каких объектов зависит Хранимая Процедура ?  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4271
Предлагаю добавить это в FAQ, так как часто бывает нужно найти определённый объект во всех вхождениях.
Например, для переименования или поиска зависимостей.
9 дек 15, 10:29    [18533743]     Ответить | Цитировать Сообщить модератору
 Re: От каких объектов зависит Хранимая Процедура ?  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
SQL2008
Предлагаю добавить это в FAQ, так как часто бывает нужно найти определённый объект во всех вхождениях.
Например, для переименования или поиска зависимостей.
Предлагаю не добавлять.
Потому что поле text короткое, содержит ошмётки текста объекта, порезанные необязательно по границам слов.
Лучше
SELECT type, name
FROM sys.objects
WHERE OBJECT_DEFINITION(object_id) LIKE N'%TableName%'
Правда, данный простой запрос будет искать объекты,
в имени которых есть фрагмент 'TableName', а не объекты с именем TableName.
И есть ещё комментарии, упомянутые выше динамические запросы, литеральные строки,
содержащие фрагмент, но не имеющие отношения к объектам базы и т.д.
9 дек 15, 11:13    [18533980]     Ответить | Цитировать Сообщить модератору
 Re: От каких объектов зависит Хранимая Процедура ?  [new]
Chippollino.01
Member

Откуда: изнемагаю
Сообщений: 35527
в syscomments еще переносы могут быть - часть сигнатуры в одной строке, часть в другой.
SELECT DISTINCT dbo.sysobjects.name, dbo.sysobjects.xtype, dbo.sysobjects.id 
FROM (SELECT syscom1.id, RIGHT(syscom1.text, 2000) + LEFT(syscom2.text, 2000) AS text 
FROM dbo.syscomments syscom1 
INNER JOIN dbo.syscomments syscom2 ON syscom1.id = syscom2.id AND syscom1.colid = syscom2.colid + 1 
UNION SELECT id,text FROM dbo.syscomments) syscom 
INNER JOIN dbo.sysobjects ON syscom.id = dbo.sysobjects.id 
WHERE (syscom.text LIKE N'%TABLE_NAME%')
order by 1
9 дек 15, 11:15    [18533986]     Ответить | Цитировать Сообщить модератору
 Re: От каких объектов зависит Хранимая Процедура ?  [new]
LSV
Member [заблокирован]

Откуда: Киев
Сообщений: 30817
Обойти все подозрительные на ненужность объекты и запросить их зависимость. Если ее нет, то пометить объекты на удаление.
Потом их удалить перед этим закриптовав. Если вдруг зря удалили, вернуть взад. Не ? :)
9 дек 15, 11:43    [18534174]     Ответить | Цитировать Сообщить модератору
 Re: От каких объектов зависит Хранимая Процедура ?  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4271
Chippollino.01
в syscomments еще переносы могут быть - часть сигнатуры в одной строке, часть в другой.

Да ладно!
Ни разу не видел, чтобы писали
SELECT * FROM [TA
BLE_NAME]

Кроме того это не будет работать, скрипт выдаст ошибку!
Специально проверил.
Конечно схему [dbo] можно перенести
SELECT * FROM [dbo].
[TABLE_NAME]

но мы ищем по имени таблицы, а оно неделимо.

автор
Правда, данный простой запрос будет искать объекты,
в имени которых есть фрагмент 'TableName', а не объекты с именем TableName.

Безусловно! Но так нужно, чтобы находить объекты в любой схеме и из выданного результата мы сумеем отобрать то, что нам необходимо.
9 дек 15, 11:51    [18534213]     Ответить | Цитировать Сообщить модератору
 Re: От каких объектов зависит Хранимая Процедура ?  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
LSV
Обойти все подозрительные на ненужность объекты и запросить их зависимость. Если ее нет, то пометить объекты на удаление.
Потом их удалить перед этим закриптовав. Если вдруг зря удалили, вернуть взад. Не ? :)
Ещё проще.
Удалить всё, что хочешь, и ждать звонков от пользователей.
Восстанавливать удалённые объекты по мере роста возмущения трудового народа!
9 дек 15, 11:57    [18534242]     Ответить | Цитировать Сообщить модератору
 Re: От каких объектов зависит Хранимая Процедура ?  [new]
msLex
Member

Откуда:
Сообщений: 8217
iap
SQL2008
Предлагаю добавить это в FAQ, так как часто бывает нужно найти определённый объект во всех вхождениях.
Например, для переименования или поиска зависимостей.
Предлагаю не добавлять.
Потому что поле text короткое, содержит ошмётки текста объекта, порезанные необязательно по границам слов.
Лучше
SELECT type, name
FROM sys.objects
WHERE OBJECT_DEFINITION(object_id) LIKE N'%TableName%'


Вместо sys.syscomments можно использовать sys.sql_modules, который содержит definition nvarchar(max)
но это, естественно, не решает проблему
iap
Правда, данный простой запрос будет искать объекты,
в имени которых есть фрагмент 'TableName', а не объекты с именем TableName.
И есть ещё комментарии, упомянутые выше динамические запросы, литеральные строки,
содержащие фрагмент, но не имеющие отношения к объектам базы и т.д.


Если исключить из рассмотрения динамический SQL (включая exec @proc_name), работу с другими объектами через SQLCLR, то
вариант с invm 18528611, с sys.dm_sql_referenced_entities, или использование sys.sql_expression_dependencies (что в принципе одно и тоже, только с разных углов ), вполне может решить задачу топик стартера.
9 дек 15, 11:57    [18534245]     Ответить | Цитировать Сообщить модератору
 Re: От каких объектов зависит Хранимая Процедура ?  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
SQL2008
ищем по имени таблицы, а оно неделимо.
Что значит "не"
9 дек 15, 11:57    [18534246]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить