Диагностика зависимостей SQL
В этом разделе описаны общие проблемы для зависимостей объектов и их решения.
Функция динамического управления sys.dm_sql_referenced_entities не возвращает зависимости на уровне столбцов
Системная функция sys.dm_sql_referenced_entities покажет любую зависимость на уровне столбцов для ссылок, привязанных к схемам. Например, функция отобразит все зависимости на уровне столбцов для индексированного представления, поскольку для индексированного представления необходима привязка к схеме. Однако если упоминаемая сущность, на которую дается ссылка, не привязана к схеме, зависимости столбцов отображаются только в том случае, если можно привязать все инструкции, в которых имеются ссылки на столбцы. Инструкции можно успешно привязать только при наличии всех объектов в момент синтаксического анализа инструкций. Если инструкцию, определенную в сущности, привязать не удается, то зависимости столбцов отображаться не будут и столбец referenced_minor_id вернет 0. Если не удается разрешить зависимости столбцов, возникает ошибка 2020. Эта ошибка не препятствует возврату запросом зависимостей на уровне объектов.
Решение
Устраните ошибки, определенные в сообщении до возникновения ошибки 2020. Например, в следующем примере кода представление Production.ApprovedDocuments определяется в столбцах Title, ChangeNumber и Status в таблице Production.Document. Объекты и столбцы, от которых зависит представление ApprovedDocuments, запрашиваются системной функцией sys.dm_sql_referenced_entities. Поскольку представление не создается при помощи предложения WITH SCHEMA_BINDING, столбцы, на которые имеются ссылки в представлении, можно изменять в ссылочной таблице. В примере изменяется столбец ChangeNumber в таблице Production.Document путем переименования его в TrackingNumber. Представление каталога вновь запрашивается для получения представления ApprovedDocuments; однако его нельзя привязать ко всем столбцам, определенным в представлении. Ошибки 207 и 2020 возвращаются с указанием проблемы. Для решения проблемы необходимо изменить представление так, чтобы отразить новое имя столбца.
USE AdventureWorks2008R2;
GO
CREATE VIEW Production.ApprovedDocuments
AS
SELECT Title, ChangeNumber, Status
FROM Production.Document
WHERE Status = 2;
GO
SELECT referenced_schema_name AS schema_name
,referenced_entity_name AS table_name
,referenced_minor_name AS referenced_column
FROM sys.dm_sql_referenced_entities ('Production.ApprovedDocuments', 'OBJECT');
GO
EXEC sp_rename 'Production.Document.ChangeNumber', 'TrackingNumber', 'COLUMN';
GO
SELECT referenced_schema_name AS schema_name
,referenced_entity_name AS table_name
,referenced_minor_name AS referenced_column
FROM sys.dm_sql_referenced_entities ('Production.ApprovedDocuments', 'OBJECT');
GO
Результатом запроса будут следующие сообщения об ошибках.
Msg 207, Level 16, State 1, Procedure ApprovedDocuments, Line 3
Invalid column name 'ChangeNumber'.
Msg 2020, Level 16, State 1, Line 1
The dependencies reported for entity "Production.ApprovedDocuments" do not include references to columns. This is either because the entity references an object that does not exist or because of an error in one or more statements in the entity. Before rerunning the query, ensure that there are no errors in the entity and that all objects referenced by the entity exist.
В следующем примере исправляется имя столбца в представлении.
USE AdventureWorks2008R2;
GO
ALTER VIEW Production.ApprovedDocuments
AS
SELECT Title,TrackingNumber, Status
FROM Production.Document
WHERE Status = 2;
GO
Зависимости на уровне столбцов не возвращаются для инструкций внутри хранимых процедур, содержащих соединения с временными таблицами. Для хранимых процедур, состоящих из нескольких инструкций, зависимости на уровне столбцов возвращаются для инструкций, не имеющих соединений с временными таблицами. Инструкции, присоединенные к временным таблицам, не будут иметь возможности получения списка зависимостей на уровне столбцов.
Столбец is_ambiguous передает несогласованные значения для определяемых пользователем функций
Значение, переданное в столбце is_ambiguous, может оказаться несогласованным для определяемых пользователем функций. Столбец is_ambiguous в представлении каталога sys.sql_expression_dependencies и динамической функции sys.dm_sql_referenced_entities указывает на неоднозначность ссылки на сущность. То есть во время выполнения может произойти разрешение сущности к определяемой пользователем функции, определяемому пользователем типу (UDT) или ссылке XQuery на столбец типа xml. В зависимости от способа обращения к определяемой пользователем функции, тип сущности может быть как понятным, так и наоборот, в результате чего в одном случае столбец is_ambiguous будет иметь значение 1 (true), а в другом случае — 0 (false). Рассмотрим следующую хранимую процедуру.
CREATE PROCEDURE dbo.p1
AS
SELECT Sales.GetOrder() FROM t1;
SELECT Sales.GetOrder();
В первой инструкции SELECT неясно, является ли Sales.GetOrder() определяемой пользователем функцией в схеме Sales или столбцом с именем Sales определяемого пользователем типа с методом, имеющим имя GetOrder(). В этом случае столбец is_ambiguous будет иметь значение 1 для упоминаемой сущности Sales.GetOrder(), на которую имеется ссылка. Во второй инструкции SELECT ссылка на Sales.GetOrder() очевидна; исходя из синтаксиса, это может быть только ссылка на определяемую пользователем функцию. В этом случае столбцу is_ambiguous присваивается значение 0. Это может вызвать несогласованность значения, переданного в столбце is_ambiguous. Поняв способ определения значения столбца is_ambiguous, можно уточнить переданные значения.
Столбец is_ambiguous имеет значение 0 (false), если:
Ясно, что ссылка указывает на определяемую пользователем функцию. То есть запрос привязан к определяемой пользователем функции, а метод определяемого пользователем типа столбца или столбец типа xml с этим именем не существует.
Ссылка дана на метод определяемого пользователем типа столбца. То есть столбец с этим методом UDT существует, а определяемая пользователем функция или столбец типа xml с именем — нет.
Столбец is_ambiguous имеет значение 1 (true), если:
Определяемая пользователем функция, метод определяемого пользователем типа столбца или столбец типа xml с именем, на которое имеется ссылка, не существует.
Имя, на которое имеется ссылка, существует для нескольких сущностей. Например, определяемая пользователем функция и метод определяемого пользователем типа столбца имеют одинаковое имя.
Для неоднозначных по своей природе сущностей столбцы referenced_database_name и referenced_schema_name могут быть недопустимыми. В качестве примера рассмотрим следующую определяемую пользователем функцию.
CREATE FUNCTION GetNextEmpHierarchyId (@empname varchar(25))
RETURNS hierarchyid
AS
BEGIN
RETURN
(
SELECT h.empid.GetDescendant((SELECT MAX(h1.empid)
FROM dbo.Employees AS h1
WHERE h1.empid.GetAncestor(1) = h.empid), NULL)
FROM dbo.Employees AS h
WHERE h.empname = @empname
)
END;
Столбцы referenced_database_name и referenced_schema_name будут недопустимыми для функции из-за вызовов метода UDT hierarchyid. Неясно, являются ли ссылки на h.empid.GetDescendant и h1.empid.GetAncestor ссылками на сущность, использующую трехкомпонентное имя (database.schema.object), или на метод UDT (table.column.method).
Решение
Вмешательство пользователя не требуется.
Столбец Referenced_id не учитывается при проверке межбазовых зависимостей
Столбец referenced_id не учитывается при проверке межбазовых зависимостей в представлении каталога sys.sql_expression_dependencies. Имя базы данных и имя схемы записываются только в том случае, если они указываются явно. Например, если имя таблицы задается в виде MyDB.MySchema.MyTable, записываются имена базы данных и схемы, но, если имя таблицы задается в виде MyDB..MyTable, записывается только имя базы данных.
Столбец referenced_id передается межбазовым ссылкам в системной функции sys.dm_sql_referenced_entities только тогда, когда упоминаемую сущность можно успешно привязать. Привязка может завершиться ошибкой по нескольким причинам, включая следующие:
База данных работает в режиме «вне сети».
Упоминаемая сущность не существует в базе данных.
Решение
Убедитесь, что база данных работает в режиме «вне сети», и проверьте наличие упоминаемой сущности в базе данных.
Столбец referenced_id имеет значение Null для упоминаемых сущностей в базе данных
Системная функция sys.dm_sql_referenced_entities и системное представление sys.sql_expression_dependencies отобразят идентификатор любой упоминаемой сущности, привязанной к схеме. Однако столбец referenced_id имеет значение NULL для непривязанных к схеме ссылок в базе данных, если не удается определить идентификатор упоминаемой сущности. Это может возникнуть в следующих случаях.
Упоминаемая сущность не существует в базе данных.
Разрешение имени зависит от вызывающего объекта. В этом случае столбцу is_caller_dependent присваивается значение 1.
Решение
Убедитесь, что упоминаемая сущность существует в базе данных. Создайте сущность, если таковая не была найдена, или — если сущность существует — убедитесь, что выполняются следующие требования.
Имя упоминаемой сущности записано правильно.
Указанное имя соответствует требованиям параметров сортировки базы данных. Если в базе данных используются параметры сортировки с учетом регистра, то указанное имя должно точно согласовываться по регистру с именем объекта. Например, идентификатор объекта с именем SalesHistory не будет найден в базе данных с параметрами сортировки с учетом регистра, если он указан как saleshistory.
Указано имя схемы объекта. Двухкомпонентное имя (schema_name.object_name) необходимо в том случае, если объект не входит в используемую по умолчанию схему вызывающего метода — sys или dbo.
Измените определение ссылающейся сущности для соответствия вышеуказанным требованиям.
Если упоминаемая сущность зависит от вызывающего метода, измените определение ссылающейся сущности путем указания двухкомпонентного имени для упоминаемой сущности. Дополнительные сведения о ссылках, зависящих от вызывающего метода, см. в разделе Создание отчета о зависимостях SQL.
Сведения о зависимостях не передаются объектам в базе данных master
Создаются и поддерживаются зависимости SQL от определяемых пользователем сущностей, созданных в базе данных master. Если зависимости SQL для сущности не передаются, выполните следующие действия.
Убедитесь, что тип сущности является допустимым для отслеживания зависимостей.
Сведения о зависимостях отслеживаются не для всех пользовательских объектов. Список типов сущностей, для которых создаются и поддерживаются сведения о зависимостях, см. в разделе Основные сведения о зависимостях SQL.
Убедитесь, что сущность не помечена как системный объект.
Запросите сущность по столбцу is_ms_shipped в представлении каталога sys.objects. Если этот столбец имеет значение 1, то сущность является системным объектом, поставляемым с SQL Server, или определяемым пользователем объектом, измененным для имитации системного объекта путем присваивания столбцу значение 1 вручную.
Решение
Если тип объекта не поддерживается, сведения о зависимостях будут недоступны.
Зависимости от системных объектов не отслеживаются. Если сущность определяется пользователем, то столбцу is_ms_shipped column необходимо присвоить значение 0, чтобы SQL Server создал зависимости и поддерживал их.
См. также