Создание отчета о зависимостях SQL
Зависимости SQL представляют собой именные ссылки, которые используются в выражениях SQL и делают одну сущность зависимой от другой. Например, представления и хранимые процедуры зависят от существования таблиц, содержащих данные, которые возвращаются этими таблицами и процедурами. Информация о зависимостях используется в следующих сценариях.
Перемещение модуля, например хранимой процедуры, из одного приложения в другое.
Прежде чем переносить модуль, необходимо выяснить, ссылается ли он на какие-либо сущности в этой и других базах данных, которые необходимо перенести вместе с модулем.
Изменение определения сущности, например добавление или удаление столбца таблицы.
Прежде чем вносить изменения в сущность, необходимо выяснить, нет ли других сущностей, зависящих от существующего определения этой сущности. Эти зависимые сущности могут привести к неожиданным результатам в случае их вызова или запроса к ним после изменения и потребовать обновления метаданных или внесения изменений в их определение.
Перенос одной или нескольких баз данных с одного сервера на другой.
Прежде чем переносить базы данных между серверами, необходимо выяснить, существуют ли в одной базе данных сущности, которые зависят от сущностей в другой базе данных. В этом случае лучше перенести эти базы данных на один и тот же сервер.
Настройка отработки отказа приложений, работающих с несколькими базами данных.
Необходимо сделать приложение постоянно доступным и использовать зеркальное отображение базы данных для отработки отказа. Приложение зависит от нескольких баз данных и должно успешно работать в случае аварийного переключения на зеркальный сервер. Поскольку зеркальное отображение создается на уровне базы данных, необходимо определить, какие базы данных критически важны для приложения, чтобы настроить создание их зеркальных отображений индивидуально. После этого можно обеспечить одновременное переключение всех баз данных на зеркальный сервер и успешную работу приложения на нем.
Поиск сущностей в приложении, выполняющих распределенные запросы с использованием четырехкомпонентных имен.
Необходимо выяснить, какие связанные серверы используются в распределенных запросах.
Поиск сущностей, которые используются в приложении и содержат ссылки в виде однокомпонентного имени и ссылки, зависящие от вызывающего.
Прежде чем развертывать приложение, можно определить, содержат ли сущности, используемые этим приложением, ссылки, которые зависят от вызывающего, или ссылки на сущности в виде однокомпонентного имени. Такие ссылки свидетельствуют о плохом стиле программирования и могут привести к неожиданному поведению приложения после его развертывания. Дело в том, что разрешение имени сущности, на которую делается ссылка, зависит от схемы вызывающего, а эта информация становится известна только после запуска программы. Когда эти ссылки найдены, можно исправить запросы, указав имя из нескольких компонентов (например, schema_name.object_name).
Дополнительные сведения о зависимостях в SQL см. в разделе Основные сведения о зависимостях SQL.
Выявление зависимостей с помощью системных представлений и функций
Для просмотра зависимостей SQL приложение SQL Server 2008 использует представление каталога sys.sql_expression_dependencies, а также такие динамические функции управления, как sys.dm_sql_referenced_entities и sys.dm_sql_referencing_entities. К этим объектам можно посылать запросы для получения информации о зависимостях сущностей, определенных пользователем.
Увидеть зависимости SQL можно также с помощью команды Просмотреть зависимости в Среда SQL Server Management Studio. Дополнительные сведения см. в разделе Как просмотреть зависимости SQL (среда SQL Server Management Studio).
Использование представления каталога sys.sql_expression_dependencies
Представление каталога sys.sql_expression_dependencies обеспечивает владельцу или администратору базы данных возможность получить информацию о зависимостях по конкретной базе данных. С помощью этого представления можно ответить на следующие общие вопросы.
Какие межсерверные и межбазовые зависимости существуют у этой базы данных?
Какие зависимости существуют внутри базы данных.
Какие сущности в базе данных имеют ссылки, зависящие от вызывающего.
Какие триггеры DDL на уровне сервера или базы данных зависят от сущностей базы данных?
Какие модули в базе данных используют пользовательский тип данных (UDT).
Представление каталога sys.sql_expression_dependencies имеет следующие ограничения.
Список зависимостей от межбазовых и межсерверных сущностей возвращается только в случае, если задано допустимое четырехкомпонентное или трехкомпонентное имя. Идентификаторы сущностей, на которые делается ссылка, не выдаются.
Список зависимостей на уровне столбцов выдается только для сущностей, связанных со схемой.
Использование функции динамического управления sys.dm_sql_referenced_entities
Функция sys.dm_sql_referenced_entities возвращает по одной строке для каждой пользовательской сущности, на которую ссылается по имени определение ссылающейся сущности. Ссылающаяся сущность может быть пользовательским объектом, а также триггером DDL на уровне сервера или базы данных. Это та же самая информация, которую возвращает функция sys.sql_expression_dependencies, но результирующий набор ограничен сущностями, к которым обращается конкретная ссылающаяся сущность. Эта функция полезна для разработчиков, отслеживающих зависимости от модулей, которые им принадлежат или на которые есть разрешение VIEW DEFINITION.
Использование динамической функции управления sys.dm_sql_referencing_entities
Функция sys.dm_sql_referencing_entities возвращает по одной строке на каждую пользовательскую сущность текущей базы данных, которая ссылается на другую пользовательскую сущность по имени. Ссылающаяся сущность может быть определяемым пользователем объектом, типом (псевдонимом или CLR UDT), коллекцией XML-схем или функцией секционирования. Эта функция может быть полезна для разработчиков, отслеживающих зависимости от принадлежащих им сущностей. Например, прежде чем изменить определяемый пользователем тип, можно с помощью этой функции выявить все сущности базы данных, которые зависят от этого типа. Заметьте, что ссылки на определяемый пользователем тип в таблице отображаются только в случае, если этот тип задан в определении вычисляемого столбца, ограничении CHECK или ограничении DEFAULT.
Примеры
Следующие примеры возвращают список зависимостей SQL с помощью представления каталога sys.sql_expression_dependencies, а также динамических функций управления sys.dm_sql_referenced_entities и sys.dm_sql_referencing_entities.
Получение списка сущностей, от которых зависит указанная сущность
Получить список сущностей, от которых зависит указанная сущность, можно с помощью запроса к каталогу sys.sql_expression_dependencies или динамических функций управления sys.dm_sql_referenced_entities и sys.dm_sql_referencing_entities. Например, можно получить список сущностей, к которым обращается какой-либо модуль (хранимая процедура или триггер).
В следующем примере создается таблица, представление и три хранимых процедуры. Эти объекты затем используются в запросах, чтобы показать, как получить информацию о зависимостях. Заметьте, что как MyView, так и MyProc3 ссылаются на таблицу Mytable. MyProc1 ссылается на представление MyView, а MyProc2 — на MyProc1.
USE AdventureWorks2008R2;
GO
-- Create entities
CREATE TABLE dbo.MyTable (c1 int, c2 varchar(32));
GO
CREATE VIEW dbo.MyView
AS SELECT c1, c2 FROM dbo.MyTable;
GO
CREATE PROC dbo.MyProc1
AS SELECT c1 FROM dbo.MyView;
GO
CREATE PROC dbo.MyProc2
AS EXEC dbo.MyProc1;
GO
CREATE PROC dbo.MyProc3
AS SELECT * FROM AdventureWorks2008R2.dbo.MyTable;
EXEC dbo.MyProc2;
GO
В следующем примере используется запрос к представлению каталога sys.sql_expression_dependencies для получения списка сущностей, на которые ссылается сущность MyProc3.
USE AdventureWorks2008R2;
GO
SELECT OBJECT_NAME(referencing_id) AS referencing_entity_name
,referenced_server_name AS server_name
,referenced_database_name AS database_name
,referenced_schema_name AS schema_name
, referenced_entity_name
FROM sys.sql_expression_dependencies
WHERE referencing_id = OBJECT_ID(N'dbo.MyProc3');
GO
Ниже приводится результирующий набор.
referencing_entity server_name database_name schema_name referenced_entity
------------------ ----------- -------------------- ----------- -----------------
MyProc3 NULL NULL dbo MyProc2
MyProc3 NULL AdventureWorks2008R2 dbo MyTable
(Обработано строк: 2)
Запрос возвращает две сущности, на которые ссылается по имени определение MyProc3. Имя сервера равно NULL, так как сущности, на которые делается ссылка, не были заданы с помощью четырехкомпонентных имен. Для MyTable отображается имя базы данных, так как сущность была задана в процедуре в виде трехкомпонентного имени.
Аналогичную информацию можно получить и с помощью функции sys.dm_sql_referenced_entities. Кроме списка имен объектов, эта функция может возвращать список зависимостей на уровне столбцов для сущностей, как связанных, так и не связанных со схемой. В следующем примере возвращается список сущностей, от которых зависит MyProc3, включая зависимости на уровне столбцов.
USE AdventureWorks2008R2;
GO
SELECT referenced_server_name AS server
, referenced_database_name AS database_name
, referenced_schema_name AS schema_name
, referenced_entity_name AS referenced_entity
, referenced_minor_name AS column_name
FROM sys.dm_sql_referenced_entities ('dbo.MyProc3', 'OBJECT');
GO
Ниже приводится результирующий набор.
server_name database_name schema_name referenced_entity column_name
----------- -------------------- ----------- ----------------- -----------
NULL NULL dbo MyProc2 NULL
NULL AdventureWorks2008R2 dbo MyTable NULL
NULL AdventureWorks2008R2 dbo MyTable c1
NULL AdventureWorks2008R2 dbo MyTable c2
(Обработано строк: 4)
Этот результирующий набор содержит те же самые две сущности, но выводятся две дополнительные строки, которые показывают зависимость от столбцов c1 и c2 в таблице MyTable. Заметьте, что в определении объекта MyProc3 для обращения к столбцам таблицы MyTable используется инструкция SELECT. Использовать этот прием программирования не рекомендуется, но компонент Database Engine все-таки возвращает список зависимостей на уровне столбцов.
До этого момента примеры показывали, как получить список сущностей, от которых какая-то сущность зависит напрямую. В следующем примере для вывода списка прямых и косвенных зависимостей от сущности используется рекурсивное обобщенное табличное выражение (CTE).
DECLARE @referencing_entity AS sysname;
SET @referencing_entity = N'MyProc3';
WITH ObjectDepends(entity_name,referenced_schema, referenced_entity, referenced_id,level)
AS (
SELECT entity_name =
CASE referencing_class
WHEN 1 THEN OBJECT_NAME(referencing_id)
WHEN 12 THEN (SELECT t.name FROM sys.triggers AS t
WHERE t.object_id = sed.referencing_id)
WHEN 13 THEN (SELECT st.name FROM sys.server_triggers AS st
WHERE st.object_id = sed.referencing_id) COLLATE database_default
END
,referenced_schema_name
,referenced_entity_name
,referenced_id
,0 AS level
FROM sys.sql_expression_dependencies AS sed
WHERE OBJECT_NAME(referencing_id) = @referencing_entity
UNION ALL
SELECT entity_name =
CASE sed.referencing_class
WHEN 1 THEN OBJECT_NAME(sed.referencing_id)
WHEN 12 THEN (SELECT t.name FROM sys.triggers AS t
WHERE t.object_id = sed.referencing_id)
WHEN 13 THEN (SELECT st.name FROM sys.server_triggers AS st
WHERE st.object_id = sed.referencing_id) COLLATE database_default
END
,sed.referenced_schema_name
,sed.referenced_entity_name
,sed.referenced_id
,level + 1
FROM ObjectDepends AS o
JOIN sys.sql_expression_dependencies AS sed ON sed.referencing_id = o.referenced_id
)
SELECT entity_name,referenced_schema, referenced_entity, level
FROM ObjectDepends
ORDER BY level;
GO
Ниже приводится результирующий набор.
entity_name referenced_schema referenced_entity level
----------- ----------------- ----------------- -----
MyProc3 dbo MyProc2 0
MyProc3 dbo MyTable 0
MyProc2 dbo MyProc1 1
MyProc1 dbo MyView 2
MyView dbo MyTable 3
(Изменено строк: 5)
В этом результирующем наборе объекты MyProc2 и MyTable возвращены как прямые зависимости, что обозначено уровнем 0. В третьей строке представлена косвенная зависимость от объекта MyProc1, на который есть ссылка в определении MyProc2. В четвертой строке представлена зависимость от объекта MyView, на который есть ссылка в определении MyProc1, а также зависимость от таблицы MyTable, на которую ссылается определение MyView.
Получив информацию об иерархических зависимостях, можно определить полный список прямых и косвенных зависимостей от данной сущности, а также порядок развертывания этих объектов при переносе в другую базу данных.
В следующем примере возвращается та же самая информация об иерархических зависимостях, но с помощью функции sys.dm_sql_referenced_entities. Отображаются сущности, от которых зависит объект MyProc3, включая зависимости на уровне столбцов.
USE AdventureWorks2008R2;
GO
DECLARE @entity AS sysname , @type AS sysname;
SET @entity = N'dbo.MyProc3';
SET @type = N'OBJECT';
WITH ObjectDepends(referenced_schema_name, referenced_entity_name, referenced_column,
referenced_id,level)
AS (
SELECT
referenced_schema_name
,referenced_entity_name
,referenced_minor_name AS referenced_column
,referenced_id
,0 AS level
FROM sys.dm_sql_referenced_entities (@entity, @type)
UNION ALL
SELECT
re.referenced_schema_name
,re.referenced_entity_name
,re.referenced_minor_name AS referenced_column
,re.referenced_id
,level + 1
FROM ObjectDepends AS o
CROSS APPLY sys.dm_sql_referenced_entities (o.referenced_schema_name + N'.' + o.referenced_entity_name, @type) AS re
)
SELECT referenced_schema_name, referenced_entity_name, referenced_column, level
FROM ObjectDepends
ORDER BY level;
GO
Получение списка сущностей, которые зависят от заданной сущности
Получить список сущностей, зависящих от указанной сущности, можно с помощью каталога sys.sql_expression_dependencies или динамической функции управления sys.dm_sql_referencing_entities. Например, если указанная сущность является таблицей, выдается список всех сущностей, ссылающихся на нее по имени в своих определениях.
В следующем примере выдается список всех сущностей, ссылающихся на сущность dbo.MyTable.
USE AdventureWorks2008R2;
GO
SELECT OBJECT_SCHEMA_NAME ( referencing_id ) AS referencing_schema_name,
OBJECT_NAME(referencing_id) AS referencing_entity_name,
COALESCE(COL_NAME(referencing_id, referencing_minor_id), '(n/a)') AS referencing_column,
referencing_class_desc, referenced_class_desc,
referenced_server_name, referenced_database_name, referenced_schema_name,
referenced_entity_name,
COALESCE(COL_NAME(referenced_id, referenced_minor_id), '(n/a)') AS referenced_column,
is_caller_dependent, is_ambiguous
FROM sys.sql_expression_dependencies AS sed
WHERE referenced_id = OBJECT_ID(N'dbo.MyTable');
GO
Аналогичную информацию можно получить и с помощью динамической функции управления sys.dm_sql_referenced_entities.
USE AdventureWorks2008R2;
GO
SELECT referencing_schema_name, referencing_entity_name, referencing_id, referencing_class_desc, is_caller_dependent
FROM sys.dm_sql_referencing_entities ('dbo.MyTable', 'OBJECT');
GO
Получение списка зависимостей на уровне столбцов
Список зависимостей на уровне столбцов можно получить с помощью функции sys.dm_sql_referenced_entities для сущностей, как связанных, так и не связанных со схемой. Список зависимостей на уровне столбцов для сущностей, связанных со схемой, можно также получить с помощью функции sys.sql_expression_dependencies.
В следующем примере с помощью функции sys.dm_sql_referenced_entities отображаются зависимости на уровне столбцов для сущностей, не связанных со схемой. В данном примере сначала создаются таблицы Table1 и Table 2, а также хранимая процедура Proc1. Процедура ссылается на столбцы b и c в таблице Table1 и столбец c2 в таблице Table2. Представление sys.dm_sql_referenced_entities запускается с помощью хранимой процедуры, которая указана в качестве ссылающейся сущности. Результирующий набор содержит строки для сущностей Table1 и Table2, на которые указывают ссылки, а также столбцы, на которые указывают ссылки в определении хранимой процедуры. Обратите внимание, что в столбце column_name в строках со ссылками на таблицы возвращается значение NULL.
USE AdventureWorks2008R2;
GO
CREATE TABLE dbo.Table1 (a int, b int, c int);
GO
CREATE TABLE dbo.Table2 (c1 int, c2 int);
GO
CREATE PROCEDURE dbo.Proc1 AS
SELECT b, c FROM dbo.Table1;
SELECT c2 FROM dbo.Table2;
GO
SELECT referenced_id, referenced_entity_name AS table_name, referenced_minor_name AS column_name
FROM sys.dm_sql_referenced_entities ('dbo.Proc1', 'OBJECT');
GO
Ниже приводится результирующий набор.
referenced_id, table_name, column_name
------------- ----------- -------------
151671588 Table1 NULL
151671588 Table1 b
151671588 Table1 c
2707154552 Table2 NULL
2707154552 Table2 c2
Вывод списка межбазовых и межсерверных зависимостей
Межбазовая зависимость создается, когда некоторая сущность ссылается на другую сущность с помощью допустимого трехкомпонентного имени. Межсерверная зависимость создается, когда некоторая сущность ссылается на другую сущность с помощью допустимого четырехкомпонентного имени. Имена сервера и базы данных записываются только в том случае, если они указываются явно. Например, если имя таблицы задается в виде MyServer.MyDB.MySchema.MyTable, записываются имена сервера и базы данных, но если имя таблицы задается в виде MyServer..MySchema.MyTable, записывается только имя сервера. Дополнительные сведения об отслеживании межсерверных и межбазовых зависимостей см. в разделе Основные сведения о зависимостях SQL.
Список межбазовых и межсерверных зависимостей можно получить с помощью функций sys.sql_expression_dependencies или sys.dm_sql_referenced_entitites.
В следующем примере возвращаются все межбазовые зависимости. Вначале в примере создается база данных db1 и две хранимые процедуры, которые ссылаются на таблицы в базах данных db2 и db3. Затем запрашивается таблица sys.sql_expression_dependencies, чтобы сообщить о наличии межбазовых зависимостей между процедурами и таблицами. Обратите внимание на то, что в столбце referenced_schema_name для упоминаемой сущности t3 возвращается значение NULL, потому что для этой сущности в определении процедуры не указано имя схемы.
CREATE DATABASE db1;
GO
USE db1;
GO
CREATE PROCEDURE p1 AS SELECT * FROM db2.s1.t1;
GO
CREATE PROCEDURE p2 AS
UPDATE db3..t3
SET c1 = c1 + 1;
GO
SELECT OBJECT_NAME (referencing_id),referenced_database_name,
referenced_schema_name, referenced_entity_name
FROM sys.sql_expression_dependencies
WHERE referenced_database_name IS NOT NULL;
GO
USE master;
GO
DROP DATABASE db1;
GO
Вывод списка ссылок, зависящих от вызывающего
Ссылка, зависящая от вызывающего, означает, что упоминаемая сущность привязывается к схеме только во время выполнения программы, поэтому разрешение идентификатора сущности зависит от схемы вызывающего, используемой по умолчанию. Обычно это называется динамической привязкой к схеме и используется в тех случаях, когда упоминаемая сущность является хранимой процедурой, расширенной хранимой процедурой или не привязанной к схеме определяемой пользователем функцией, которая вызывается с помощью инструкции EXECUTE без указания имени схемы. Например, ссылка на сущность в формате EXECUTE MySchema.MyProc не зависит от вызывающего, а ссылка в формате EXECUTE MyProc зависит.
Ссылки, зависящие от вызывающего, могут вызвать неожиданное поведение во время выполнения модуля с такими ссылками. Рассмотрим в качестве примера следующую хранимую процедуру, которая обращается к процедуре по однокомпонентному имени.
CREATE PROCEDURE dbo.Proc1
AS EXECUTE dbo.Proc2;
GO
Когда выполняется процедура Proc1, Proc2 привязывается к схеме вызывающего. Предположим, что процедура Proc1 может выполняться пользователем User1 со схемой S1 по умолчанию и пользователем User2 со схемой S2 по умолчанию. Если процедура Proc1 выполняется пользователем User1, упоминаемая сущность разрешается в объект S1.Proc2. Если процедура Proc1 выполняется пользователем User2, упоминаемая сущность разрешается в объект S2.Proc2. В результате идентификатор процедуры Proc2 не может быть разрешен до выполнения процедуры Proc1. Поэтому столбец is_caller_dependent представления sys.sql_expression_dependencies и функции sys.dm_sql_referenced_entities function имеет значение 1. Когда выполняется процедура Proc1, компонент Database Engine ищет упоминаемую сущность Proc2 в схеме, используемой для вызывающего по умолчанию. Если сущность не найдена, проверяется схема dbo. Если процедура Proc2 не найдена в схеме dbo, идентификатор процедуры Proc2 не может быть разрешен и инструкция не выполняется. Во избежание ошибок приложения рекомендуется обращаться к сущностям базы данных по двухкомпонентным именам.
В следующем примере выдается список сущностей текущей базы данных, в которых есть ссылки, зависящие от вызывающего.
SELECT OBJECT_NAME(referencing_id) AS referencing_entity, referenced_database_name,
referenced_schema_name, referenced_entity_name, referenced_id
FROM sys.sql_expression_dependencies
WHERE is_caller_dependent = 1;
Вывод списка сущностей, использующих заданный пользовательский тип (UDT).
В следующем примере выдается список сущностей текущей базы данных, в определениях которых содержатся ссылки на заданный тип данных. Результирующий набор показывает, что этот тип используется двумя хранимыми процедурами. Этот тип используется также в определениях нескольких столбцов таблицы HumanResources.Employee, но поскольку он не содержится в определении вычисляемого столбца, ограничении CHECK или ограничении DEFAULT в таблице, никакие строки для таблицы не возвращаются.
USE AdventureWorks2008R2;
GO
SELECT referencing_schema_name, referencing_entity_name, referencing_id, referencing_class_desc, is_caller_dependent
FROM sys.dm_sql_referencing_entities ('dbo.Flag', 'TYPE');
GO
Получение списка зависимостей триггера DDL на уровне сервера
Получить список зависимостей триггеров DDL на уровне сервера с помощью функций sys.sql_expression_dependencies и sys.dm_sql_referencing_entities можно только в случае, если контекст установлен на базу данных master). Для функции sys.dm_sql_referenced_entities контекстом может быть любая база данных.
В следующем примере выполняется запрос к представлению sys.sql_expression_dependencies для получения списка зависимостей триггеров DDL на уровне сервера.
USE master;
GO
SELECT OBJECT_NAME(referencing_id) AS referencing_entity, referencing_class_desc, referenced_server_name, referenced_database_name, referenced_schema_name, referenced_entity_name, referenced_id
FROM sys.sql_expression_dependencies
WHERE referencing_class = 13;
См. также