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

Откуда: Москва
Сообщений: 4901
Господа,
есть такая задача -- имеется произвольный запрос.

SELECT A.F1, A.F2, B.F3 
FROM A 
INNER JOIN B ON A.ID = B.A_ID 
WHERE B.F4 > 100


Надо получить список таблиц и список колонок каждой, которые используются в запросе.

В данном случае это

A F1
A F2
A ID
B F3
B A_ID
B F4

Можно это решить программно или каким-то лаф хаком ?

Запрос может быть любой сложности

SELECT * FROM A
OUTER APPLY 
( 
SELECT TOP 1 * FROM B
WHERE B.F1 > A.F1
) B
26 сен 16, 14:50    [19708857]     Ответить | Цитировать Сообщить модератору
 Re: список зависимых таблиц и колонок  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4901
Динамические запросы не рассматриваем
26 сен 16, 15:09    [19708956]     Ответить | Цитировать Сообщить модератору
 Re: список зависимых таблиц и колонок  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
a_voronin
Можно это решить программно или каким-то лаф хаком ?

Запрос может быть любой сложности
Можно. Получить оценочный план выполнения и проанализировать его.
Проблема только в получении этого самого плана на стороне сервера. Самый простой способ - через CLR. Примерно так:
    private static string ObtainEstimatedExecutionPlan(string AQuery)
    {
        using (var cn = new SqlConnection("Context Connection=true"))
        {
            cn.Open();

            try
            {
                using (var c = new SqlCommand("set showplan_xml on;", cn))
                {
                    c.ExecuteNonQuery();

                    c.CommandText = AQuery;
                    using (var r = c.ExecuteReader())
                        return (r.Read()) ? r.GetString(0) : "";
                }
            }
            catch (SqlException e)
            {
                return "";
            }
        }
    }
26 сен 16, 15:27    [19709072]     Ответить | Цитировать Сообщить модератору
 Re: список зависимых таблиц и колонок  [new]
TaPaK
Member

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

интересно, а выдрать его сразу после запроса можно?
типа
SELECT qp.query_plan, 
FROM sys.dm_exec_cached_plans AS CP
CROSS APPLY sys.dm_exec_sql_text( plan_handle)AS SQLText
CROSS APPLY sys.dm_exec_query_plan( plan_handle)AS QP
WHERE 
	cp.cacheobjtype = 'Compiled Plan' AND
	text = 'SELECT * FROM Test'
26 сен 16, 15:33    [19709111]     Ответить | Цитировать Сообщить модератору
 Re: список зависимых таблиц и колонок  [new]
_Utimate
Guest
a_voronin,

1. С помощью SQL Dependencies можно получить только малую толику информации - и только для программных объектов
(как хорошее решение - явно не подходит)

2. Писать парсилку текста
(занятие не благодарное)

3. Разбирать как XML - Estimated Execution plan
Сделать только средствами T-SQL -не уверен что возможно,
я схожую задачу решал скриптовым языком VB.

Действия примерно такие:
а. set SHOW_PLAN_XML ON
б. Шлем наше выражение ()
в. Получаем план выполнения
г. Парсим его на предмет обращения к таблицам полям
(дело муторное, и возможно долгое - нужно будет разобраться какие ноды имеют значение)
д. на парочке тестовых несложных запросов настраиваем всё
е. Профит

В моей задаче не было парсинга XML-а до уровня полей - я изучал использование индексов в куче запросов, задача волне выполнимая.

Если у самого есть более интересные варианты решения - поделитесь плиз - интересная задачка.

С уважением :)
26 сен 16, 15:39    [19709154]     Ответить | Цитировать Сообщить модератору
 Re: список зависимых таблиц и колонок  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
TaPaK
интересно, а выдрать его сразу после запроса можно?
Можно. Только вот запрос нужно сначала выполнить. И даже после выполнения плана может не быть в кеше.
26 сен 16, 15:53    [19709242]     Ответить | Цитировать Сообщить модератору
 Re: список зависимых таблиц и колонок  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4901
Вот что получилось

DECLARE @QUERY NVARCHAR(MAX) = 'SELECT * FROM [INFORMATION_SCHEMA].[COLUMNS]';-- запрос на анализ
SET @QUERY = CONCAT('/*', NEWID(), '*/', @QUERY);

EXEC (@QUERY);

SELECT qp.query_plan 
FROM sys.dm_exec_cached_plans AS CP
CROSS APPLY sys.dm_exec_sql_text( plan_handle)AS SQLText
CROSS APPLY sys.dm_exec_query_plan( plan_handle)AS QP
WHERE cp.cacheobjtype = 'Compiled Plan' AND text = @QUERY;

WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS a) 
SELECT * FROM 
(
	SELECT DISTINCT T2.cr.value( '(@Schema)[1]', 'sysname') AS TABLE_SCHEMA, T2.cr.value( '(@Table)[1]', 'sysname') AS TABLE_NAME, T2.cr.value( '(@Column)[1]', 'sysname') AS COLUMN_NAME
	FROM 
	(
		SELECT query_plan-- = CAST(REPLACE(CAST(qp.query_plan AS NVARCHAR(MAX)), 'xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"', '') AS XML) 
		FROM sys.dm_exec_cached_plans AS CP
		CROSS APPLY sys.dm_exec_sql_text( plan_handle)AS SQLText
		CROSS APPLY sys.dm_exec_query_plan( plan_handle)AS QP
		WHERE cp.cacheobjtype = 'Compiled Plan' AND text = @QUERY
	) CP
	CROSS APPLY CP.query_plan.nodes('//a:ColumnReference') as T2(cr)
) A
WHERE TABLE_SCHEMA IS NOT NULL
ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
26 сен 16, 16:04    [19709310]     Ответить | Цитировать Сообщить модератору
 Re: список зависимых таблиц и колонок  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
a_voronin,

ну если результат запрос в принципе не важен, то можно и SET FMTONLY ON на запрос.


автор
И даже после выполнения плана может не быть в кеше.

ну и может есть в этом что-то...
26 сен 16, 16:12    [19709375]     Ответить | Цитировать Сообщить модератору
 Re: список зависимых таблиц и колонок  [new]
iljy
Member

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

declare @t1 table (x int, y int)
declare @t2 table (a int, b int)

select top(0) t1.*, t2.a
from @t1 t1 join @t2 t2 on t1.x = t2.a
for xml auto, xmlschema


получается вполне себе адекватный XML, который можно легко разбирать, в том числе, на стороне сервера.
26 сен 16, 17:04    [19709807]     Ответить | Цитировать Сообщить модератору
 Re: список зависимых таблиц и колонок  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4901
iljy
a_voronin,

declare @t1 table (x int, y int)
declare @t2 table (a int, b int)

select top(0) t1.*, t2.a
from @t1 t1 join @t2 t2 on t1.x = t2.a
for xml auto, xmlschema


получается вполне себе адекватный XML, который можно легко разбирать, в том числе, на стороне сервера.


Это не решает поставленную задачу. Вы получите колонки из SELECT листа, но не из ON, WHERE и прочих мест
26 сен 16, 18:08    [19710252]     Ответить | Цитировать Сообщить модератору
 Re: список зависимых таблиц и колонок  [new]
iljy
Member

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

а, вам вообще все поля нужны? тогда только парсить. План может не помочь - оптимизатор выкидывает неиспользуемые поля, даже если они явно в запросе упомянуты.
26 сен 16, 18:15    [19710302]     Ответить | Цитировать Сообщить модератору
 Re: список зависимых таблиц и колонок  [new]
a_voronin
Member

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

Здесь вполне всё получилось

19709310
26 сен 16, 20:05    [19710862]     Ответить | Цитировать Сообщить модератору
 Re: список зависимых таблиц и колонок  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
iljy
a_voronin,

а, вам вообще все поля нужны? тогда только парсить. План может не помочь - оптимизатор выкидывает неиспользуемые поля, даже если они явно в запросе упомянуты.

Звучит странно, есть где прочитать? Или "используемые" вы имеете ввиду предикаты?
26 сен 16, 20:06    [19710864]     Ответить | Цитировать Сообщить модератору
 Re: список зависимых таблиц и колонок  [new]
iljy
Member

Откуда:
Сообщений: 8711
a_voronin
iljy,

Здесь вполне всё получилось

19709310


А вот здесь
use tempdb
create table t1(x int primary key, y int)
create table t2(a int primary key, b int)

insert t1
select top 500000 ROW_NUMBER() over(order by 1/0), CHECKSUM(newid())
from master.sys.objects, master.sys.objects o2, master.sys.objects o3

insert t2
select top 50000 ROW_NUMBER() over(order by 1/0), CHECKSUM(newid())
from master.sys.objects, master.sys.objects o2, master.sys.objects o3

go


select  t1.*
from t1 left join t2 on t1.x = t2.a

go
drop table t1
drop table t2


не получится.
26 сен 16, 22:18    [19711217]     Ответить | Цитировать Сообщить модератору
 Re: список зависимых таблиц и колонок  [new]
iljy
Member

Откуда:
Сообщений: 8711
TaPaK
Звучит странно, есть где прочитать? Или "используемые" вы имеете ввиду предикаты?


имею ввиду колонки, используемые в "ON, WHERE и прочих местах".
26 сен 16, 22:20    [19711228]     Ответить | Цитировать Сообщить модератору
 Re: список зависимых таблиц и колонок  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
Можно еще так извратиться:
use tempdb;
create table t1(x int primary key, y int);
create table t2(a int primary key, b int);
go

declare @proc sysname = N'dbo.' + quotename(cast(newid() as nvarchar(36)));
exec(N'create procedure ' + @proc + N'as select  t1.* from t1 left join t2 on t1.x = t2.a');

select
 referenced_entity_name, referenced_minor_name
from
 sys.dm_sql_referenced_entities(@proc, 'object')
where
 referenced_minor_id > 0;

exec('drop procedure ' + @proc);
go

drop table t1, t2;
go
26 сен 16, 23:26    [19711363]     Ответить | Цитировать Сообщить модератору
 Re: список зависимых таблиц и колонок  [new]
Ferdipux
Member

Откуда: Москва
Сообщений: 594
a_voronin
Надо получить список таблиц и список колонок каждой, которые используются в запросе.
...
Можно это решить программно или каким-то лаф хаком ?

Решал, по методике статьи Getting to the Crown Jewels с MSDN. Получаем в итоге parsing tree объект и ходим по нему, вытаскивая нужное. Из недостатков - нужно где-то взять dll парсера, обычно идут вместе с SSMS, и лицензионные вопросы (это если будете продавать).
27 сен 16, 15:29    [19714032]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить