Creación de informes de dependencias SQL

La vista de catálogo sys.sql_expression_dependencies permite al propietario o al administrador de la base de datos crear informes de dependencias para una base de datos determinada. El uso de esta vista permite responder a preguntas globales como las siguientes:

• ¿Qué dependencias entre servidores o entre bases de datos tiene la base de datos?

• ¿Qué dependencias existen dentro de la base de datos?

• ¿Qué entidades de la base de datos tienen referencias dependientes del autor de la llamada?

• ¿Qué desencadenadores DLL a nivel de servidor o a nivel de base de datos tienen dependencias en entidades de la base de datos?

• ¿Qué módulos de la base de datos usan un tipo definido por el usuario (UDT)?

sys.sql_expression_dependencies tiene las limitaciones siguientes:

• Las dependencias en entidades entre servidores y entre bases de datos se devuelven sólo cuando se especifica un nombre válido de tres o cuatro partes. No se devuelven los identificadores de las entidades a las que se hace referencia.

• Sólo se informa acerca de las dependencias del nivel de columna para aquellas entidades enlazadas a un esquema.

La función sys.dm_sql_referenced_entities devuelve una fila para cada entidad definida por el usuario a la que se hace referencia por el nombre en la definición de la entidad de referencia especificada. La entidad de referencia puede ser un objeto definido por el usuario, un desencadenador DDL a nivel de servidor o un desencadenador DDL a nivel de la base de datos. Ésta es la misma información que devuelve sys.sql_expression_dependencies. Sin embargo, el conjunto de resultados se limita a las entidades a las que se hace referencia mediante la entidad de referencia especificada. Esta función es útil para programadores que desean realizar el seguimiento de dependencias en los módulos de los que son propietarios o en los que tienen el permiso VIEW DEFINITION.

La función sys.dm_sql_referencing_entities devuelve una fila para cada entidad definida por el usuario en la base de datos actual que hace referencia por el nombre a otra entidad definida por el usuario. La entidad de referencia puede ser un objeto definido por el usuario, un tipo (alias o CLR UDT), una colección de esquemas XML o una función de partición. Esta función es útil para programadores que desean realizar un seguimiento de las dependencias en las entidades que poseen. Por ejemplo, antes de modificar un tipo definido por el usuario, un programador puede usar la función para determinar todas las entidades en la base de datos que dependen de ese tipo. Observe que no se notifican las referencias a un tipo definido por el usuario en una tabla, a menos que el tipo se especifique en la definición de una columna calculada, de una restricción CHECK o de una restricción DEFAULT.

Puede consultar la vista de catálogo sys.sql_expression_dependencies o la función de administración dinámica sys.dm_sql_referenced_entities para devolver una lista de entidades de las que depende una entidad especificada. Por ejemplo, puede devolver una lista de entidades a las que se hace referencia mediante un módulo, como puede ser un procedimiento almacenado o un desencadenador.

El ejemplo siguiente crea una tabla, una vista y tres procedimientos almacenados. Estos objetos se usan en consultas posteriores para mostrar cómo crear informes de información de dependencia. Observe que tanto MyView como MyProc3 hacen referencia a Mytable. MyProc1 hace referencia a MyView y MyProc2 hace referencia a MyProc1.

USE AdventureWorks;
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 AdventureWorks.dbo.MyTable;
   EXEC dbo.MyProc2;
GO

El ejemplo siguiente consulta la vista de catálogo sys.sql_expression_dependencies para devolver las entidades a las que se hace referencia mediante MyProc3.

USE AdventureWorks;
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

Éste es el conjunto de resultados.

referencing_entity server_name database_name  schema_name referenced_entity

------------------ ----------- -------------  ----------- -----------------

MyProc3            NULL        NULL           dbo         MyProc2

MyProc3            NULL        AdventureWorks dbo         MyTable

(2 filas afectadas)

Se devuelven las dos entidades a las que se hace referencia por el nombre en la definición de MyProc3. El nombre del servidor es NULL porque las entidades a las que se hace referencia no se especifican mediante un nombre de cuatro partes válido. El nombre de la base de datos se muestra para MyTable porque la entidad se definió en el procedimiento utilizando un nombre de tres partes válido.

Se puede devolver información similar utilizando sys.dm_sql_referenced_entities. Además de crear informes de los nombres de objetos, esta función se puede utilizar para devolver las dependencias del nivel de columna en entidades enlazadas a un esquema y en entidades no enlazadas a un esquema. El ejemplo siguiente devuelve las entidades de las que depende MyProc3, incluidas las dependencias del nivel de columna.

USE AdventureWorks;
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

Éste es el conjunto de resultados.

server_name database_name     schema_name  referenced_entity  column_name

----------- ----------------- -----------  -----------------  -----------

NULL        NULL              dbo          MyProc2            NULL

NULL        AdventureWorks    dbo          MyTable            NULL

NULL        AdventureWorks    dbo          MyTable            c1

NULL        AdventureWorks    dbo          MyTable            c2

(4 filas afectadas)

En este conjunto de resultados, se devuelven las dos entidades iguales. Sin embargo, se devolverán dos filas adicionales que muestran la dependencia en las columnas c1 y c2 en MyTable. Observe que en la definición de MyProc3, se utilizó una instrucción SELECT * para hacer referencia a las columnas de MyTable. No se recomienda emplear esta práctica al escribir código; no obstante, Database Engine (Motor de base de datos) continúa realizando el seguimiento de las dependencias a nivel de columna.

Hasta este punto, los ejemplos han ilustrado cómo devolver las entidades de las que una entidad depende directamente. El ejemplo siguiente usa una expresión de tabla común recursiva (CTE) para devolver todas las dependencias directas e indirectas de una entidad.

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

Éste es el conjunto de resultados.

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 filas afectadas)

En este conjunto de resultados, MyProc2 y MyTable se devuelven como dependencias directas, tal y como indica el valor de nivel 0. La tercera fila muestra la dependencia indirecta en MyProc1, a la que se hace referencia en la definición de MyProc2. La cuarta fila muestra la dependencia en MyView, a la que se hace referencia en la definición de MyProc1 y, por último, una dependencia en MyTable, a la que se hace referencia en la definición de MyView.

Al devolver la información de dependencia jerárquica, puede determinar la lista completa de dependencias directas e indirectas en una entidad determinada e inferir el orden en el que se deberían implementar esos objetos cuando sea necesario moverlos a otra base de datos.

El ejemplo siguiente devuelve la misma información de dependencia jerárquica mediante la función sys.dm_sql_referenced_entities. Se devuelven las entidades de las que depende MyProc3, incluidas las dependencias del nivel de columna.

USE AdventureWorks;
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

Puede consultar la vista de catálogo sys.sql_expression_dependencies o la función de administración dinámica sys.dm_sql_referencing_entities para devolver una lista de entidades que dependen de la entidad especificada. Por ejemplo, si la entidad especificada es una tabla, se devuelven todas las entidades que hacen referencia a esa tabla por nombre en su definición.

El ejemplo siguiente devuelve las entidades que hacen referencia a la entidad dbo.MyTable.

USE AdventureWorks;
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

Se puede devolver información similar utilizando la función de administración dinámica sys.dm_sql_referenced_entities.

USE AdventureWorks;
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

Se pueden crear informes de las dependencias del nivel de columna utilizando sys.dm_sql_referenced_entities para las entidades enlazadas a un esquema y las entidades no enlazadas a un esquema. Asimismo, se pueden crear informes de las dependencias del nivel de columna en entidades enlazadas a un esquema utilizando sys.sql_expression_dependencies.

El ejemplo siguiente consulta sys.dm_sql_referenced_entities para crear informes de dependencias del nivel de columna en entidades no enlazadas a un esquema. En el ejemplo se crea primero Table1 y Table 2 y el procedimiento almacenado Proc1. El procedimiento hace referencia a las columnas b y c en Table1 y a la columna c2 en Table2. La vista sys.dm_sql_referenced_entities se ejecuta con el procedimiento almacenado especificado como la entidad de referencia. El conjunto de resultados contiene las filas de las entidades Table1 y Table2 y las columnas a las que se hace referencia en la definición del procedimiento almacenado. Observe que en la columna column_name se devuelve NULL en las filas que hacen referencia a las tablas.

USE AdventureWorks;
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

Éste es el conjunto de resultados.

referenced_id, table_name,  column_name

-------------  -----------  -------------

151671588      Table1       NULL

151671588      Table1       b

151671588      Table1       c

2707154552     Table2       NULL

2707154552     Table2       c2

Se crea una dependencia entre bases de datos cuando una entidad realiza una referencia a otra entidad utilizando un nombre de tres partes válido. Se crea una referencia entre servidores cuando una entidad realiza una referencia a otra entidad utilizando un nombre de cuatro partes válido. Los nombres del servidor y de la base de datos sólo se registran cuando se especifica el nombre explícitamente. Por ejemplo, cuando se especifica como MyServer.MyDB.MySchema.MyTable, se registran los nombres de la base de datos y servidor. Sin embargo, cuando se especifica como MyServer..MySchema.MyTable, sólo se registra el nombre del servidor. Para obtener más información sobre cómo se realiza el seguimiento de dependencias entre servidores y entre bases de datos, consulte Descripción de las dependencias SQL.

Se pueden crear informes de las dependencias entre servidores y entre bases de datos mediante sys.sql_expression_dependencies o sys.dm_sql_referenced_entitites.

El siguiente ejemplo devuelve todas las dependencias entre bases de datos. El ejemplo crea primero la base de datos db1 y dos procedimientos almacenados que hacen referencia a tablas en las bases de datos db2 y db3. A continuación, se consulta la tabla sys.sql_expression_dependencies para crear informes de las dependencias entre bases de datos que existen entre los procedimientos y las tablas. Observe que NULL se devuelve en la columna referenced_schema_name para la entidad de referencia t3 porque no se especificó un nombre de esquema para esa entidad en la definición del procedimiento.

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

Una referencia dependiente del autor de la llamada significa que el enlace de esquema de la entidad a la que se hace referencia se produce en tiempo de ejecución y, por lo tanto, la resolución del identificador de la entidad depende del esquema predeterminado del autor de la llamada. Esto se conoce normalmente como enlace de esquema dinámico y se produce cuando la entidad de referencia es un procedimiento almacenado, un procedimiento almacenado extendido o una función definida por el usuario no enlazada a un esquema llamada en una instrucción EXECUTE sin especificar un nombre de esquema. Por ejemplo, una referencia a una entidad en el formato EXECUTE MySchema.MyProc no depende del autor de la llamada; sin embargo, una referencia en el formato EXECUTE MyProc depende del que realiza la llamada.

Las referencias dependientes del autor de la llamada pueden producir un comportamiento inesperado cuando se ejecuta el módulo en el que están referenciadas. Por ejemplo, tenga en cuenta el procedimiento almacenado siguiente, que hace referencia a un procedimiento utilizando un nombre de una parte.

CREATE PROCEDURE dbo.Proc1
AS EXECUTE dbo.Proc2;

Cuando se ejecuta Proc1, Proc2 se enlaza al esquema del autor de la llamada. Suponga que Proc1 es ejecutado por User1, que tiene un esquema predeterminado de S1 y es ejecutado por User2, que tiene un esquema predeterminado de S2. Cuando User1 ejecuta Proc1, la entidad de referencia se resuelve en S1.Proc2. Cuando User2 ejecuta Proc1, la entidad de referencia se resuelve en S2.Proc2. Debido a este comportamiento, el identificador de Proc2 no se puede resolver hasta que se ejecute Proc1. Por lo tanto, la columna is_caller_dependent se establece en 1 en la vista sys.sql_expression_dependencies y en la función sys.dm_sql_referenced_entities. Cuando se ejecuta Proc1, Database Engine (Motor de base de datos) buscará la entidad de referencia Proc2 en el esquema predeterminado del autor de la llamada. Si no se encuentra, se comprueba el esquema dbo. Si Proc2 no se puede encontrar en el esquema dbo, no se podrá resolver el identificador de Proc2 y la instrucción producirá un error. Recomendamos especificar nombres de dos partes cuando se haga referencia a las entidades de bases de datos a fin de eliminar posibles errores en las aplicaciones.

El ejemplo siguiente devuelve cada entidad en la base de datos actual que contiene una referencia dependiente del autor de la llamada.

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;

El ejemplo siguiente devuelve cada entidad en la base de datos actual que hace referencia al tipo especificado en su definición. El conjunto de resultados muestra que dos procedimientos almacenados usan ese tipo. El tipo también se usa en la definición de varias columnas en la tabla HumanResources.Employee. Sin embargo, como el tipo no está en la definición de una columna calculada, de una restricción CHECK o de una restricción DEFAULT de la tabla, no se devolverá ninguna fila para la tabla.

USE AdventureWorks;
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

Puede hacer informes sobre las dependencias de desencadenadores DDL a nivel de servidor utilizando sys.sql_expression_dependencies y sys.dm_sql_referencing_entities únicamente cuando el contexto esté establecido en la base de datos maestra. Cuando se usa la función sys.dm_sql_referenced_entities, el contexto puede ser cualquier base de datos.

El ejemplo siguiente consulta la vista de sys.sql_expression_dependencies para hacer informes sobre dependencias del desencadenador DDL a nivel de servidor.

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;