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

Откуда: SPB
Сообщений: 954
Добрый день. Подскажите, возможно как-то вытянуть все запросы из хранимок (порядка 300 штук) в которых участвует таблица. Получить именно запросы из хранимок, а не сами хранимки. Спасибо.
21 авг 15, 17:34    [18052907]     Ответить | Цитировать Сообщить модератору
 Re: Получить все запросы в которых участвует таблица  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21484
А кто мешает слить все тексты хранимок в один файл и выпарсить из них нужные запросы?
21 авг 15, 18:00    [18053032]     Ответить | Цитировать Сообщить модератору
 Re: Получить все запросы в которых участвует таблица  [new]
RasimS
Member

Откуда: SPB
Сообщений: 954
Akina,
Никто не мешает. Просто думал, есть какие-то более простые решения.
21 авг 15, 18:12    [18053072]     Ответить | Цитировать Сообщить модератору
 Re: Получить все запросы в которых участвует таблица  [new]
invm
Member

Откуда: Москва
Сообщений: 9913
RasimS
Просто думал, есть какие-то более простые решения.
Есть, но без гарантии.
Если планы процедур в кеше и обращение к таблице не через DSQL, то примерно так:
use tempdb;
go

create table dbo.t (id int, v int);
go

create procedure dbo.p1
 @id int
as
begin
 set nocount on;
 
 select id, v from dbo.t where id = @id;
 select id, v from dbo.t where id < @id;
end;
go

create procedure dbo.p2
 @id int,
 @v int
as
begin
 set nocount on;
 
 update dbo.t set v = @v where id = @id;
end;
go

exec dbo.p1 1;
exec dbo.p2 1, 1;
go

declare @table sysname = 'dbo.t';

with xmlnamespaces(default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
select
 r.referencing_schema_name, r.referencing_entity_name, c.sql_text
from
 sys.dm_sql_referencing_entities(@table, 'object') r cross apply
 (select quotename(db_name()), quotename(object_schema_name(object_id(@table, 'U'))), quotename(object_name(object_id(@table, 'U')))) o(database_name, schema_name, table_name) cross apply
 (select top (1) plan_handle, sql_handle from sys.dm_exec_procedure_stats where database_id = db_id() and object_id = r.referencing_id) ps cross apply
 sys.dm_exec_query_plan(ps.plan_handle) qp cross apply
 (select qp.query_plan.query('//StmtSimple[.//Object[@Database = sql:column("o.database_name") and @Schema = sql:column("o.schema_name") and @Table = sql:column("o.table_name")]]')) x(stmt) cross apply
 (select replace(t.n.value('@QueryHash', 'varchar(30)'), '0x', '') from x.stmt.nodes('/StmtSimple') t(n)) a(s) cross apply
 (select convert(binary(8), right('0' + a.s, 16), 2)) b(query_hash) cross apply
 (select top (1) statement_start_offset, statement_end_offset from sys.dm_exec_query_stats where sql_handle = ps.sql_handle and query_hash = b.query_hash) qs cross apply
 sys.dm_exec_sql_text(ps.sql_handle) t cross apply
 (select substring(t.text, (qs.statement_start_offset / 2) + 1, (case qs.statement_end_offset when -1 then datalength(t.text) else qs.statement_end_offset end - qs.statement_start_offset) / 2 + 1)) c(sql_text);
go

drop procedure dbo.p1, dbo.p2;
drop table dbo.t;
go

Если планов в кеше нет, то можно их туда поместить, нагенерив и выполнив вызовы процедур вместе с set fmtonly on.
Но в этом случае получится вытащить только select'ы.
21 авг 15, 23:25    [18053926]     Ответить | Цитировать Сообщить модератору
 Re: Получить все запросы в которых участвует таблица  [new]
RasimS
Member

Откуда: SPB
Сообщений: 954
invm,
Спасибо огромное за скрипт. Попробуем
22 авг 15, 00:58    [18054075]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить