Creación de informes de dependencias SQL
Las dependencias SQL son las referencias por nombre utilizadas en expresiones SQL para hacer que una entidad definida por el usuario dependa de otra entidad. Por ejemplo, las vistas y procedimientos almacenados dependen de la existencia de tablas que contienen datos devueltos por la vista o procedimiento. Realizar informes sobre la información de dependencia es útil para los siguientes escenarios:
Mover un módulo como, por ejemplo, un procedimiento almacenado de una aplicación a otra.
Antes de mover un módulo, puede determinar si existen entidades de bases de datos o entre bases de datos referenciadas por el módulo que deban moverse junto con el módulo.
Modificar la definición de una entidad como, por ejemplo, agregar o quitar una columna en una tabla.
Antes de modificar una entidad, se puede determinar si hay otras entidades que dependen de la definición actual de la entidad. Estas entidades dependientes pueden generar resultados inesperados cuando se realiza una consulta sobre ellas o se invocan después de la modificación. Asimismo, es posible que requieran una operación de actualización de metadatos o una modificación en su definición.
Mover una o varias bases de datos de un servidor a otro.
Antes de mover las bases de datos a otro servidor, puede determinar si las entidades de una base de datos tienen dependencias en entidades de otra base de datos. De esta manera, sabe que deberá mover esas bases de datos al mismo servidor.
Configurar la conmutación por error para aplicaciones que abarcan varias bases de datos.
Desea asegurarse de que su aplicación está disponible en todo momento y usar el reflejo de base de datos como estrategia de conmutación por error. La aplicación depende de varias bases de datos y desea asegurarse de que se puede ejecutar correctamente cuando se conmuta al servidor reflejado tras un error. Ya que el reflejo funciona a nivel de la base de datos, deberá determinar qué bases de datos son críticas para la aplicación, de modo que se configure el reflejo para cada una de ellas de forma independiente. A continuación, puede asegurarse de que todas las bases de datos conmutan por error a la vez, con lo que se garantiza que la aplicación funcionará en el servidor reflejado.
Buscar en una aplicación entidades que ejecutan consultas distribuidas mediante los nombres de cuatro partes.
Desea conocer qué servidores vinculados se utilizan en las consultas distribuidas.
Buscar en una aplicación entidades utilizadas que contienen referencias dependientes del autor de la llamada o referencias con nombres de una parte.
Antes de implementar una aplicación, puede determinar si las entidades utilizadas por la aplicación van a contener referencias dependientes del autor de la llamada o referencias a entidades que sólo utilizan el nombre de una parte. Este tipo de referencias revelan técnicas de programación inadecuadas y pueden provocan un comportamiento inesperado cuando se implemente la aplicación. Esto se debe a que la resolución (enlace) de la entidad a la que se hace referencia depende del esquema del autor de la llamada y esta información sólo se determina durante el tiempo de ejecución. Después de buscar estas referencias, las consultas se pueden corregir especificando un nombre de varias partes adecuado como schema_name.object_name.
Para obtener más información sobre dependencias SQL, consulte Descripción de las dependencias SQL.
Crear informes de dependencias utilizando vistas y funciones del sistema
Para ver dependencias SQL, SQL Server 2008 proporciona la vista de catálogo sys.sql_expression_dependencies y las funciones de administración dinámica sys.dm_sql_referenced_entities y sys.dm_sql_referencing_entities. Puede consultar estos objetos para que devuelvan información de dependencia en entidades definidas por el usuario.
Las dependencias SQL también se pueden ver con Ver dependencias en SQL Server Management Studio. Para obtener más información, consulte Cómo ver las dependencias SQL (SQL Server Management Studio).
Usar la vista de catálogo sys.sql_expression_dependencies
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.
Usar la función de administración dinámica sys.dm_sql_referenced_entities
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.
Usar la función de administración dinámica sys.dm_sql_referencing_entities
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.
Ejemplos
Los siguientes ejemplos devuelven dependencias SQL mediante la vista de catálogo sys.sql_expression_dependencies y las funciones de administración dinámica sys.dm_sql_referenced_entities y sys.dm_sql_referencing_entities.
Crear informes de entidades de las que depende una entidad especificada
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
Crear informes de entidades que dependen de una entidad especificada
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
Crear informes de dependencias del nivel de columna
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
Crear informes de las dependencias entre servidores y entre bases de datos
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
Crear informes de referencias dependientes del autor de la llamada
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;
Crear informes de entidades que utilizan un UDT especificado
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
Crear informes de dependencias del desencadenador DDL a nivel de servidor
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;
Vea también