Viendo las dependencias de un objeto en SQL Server

Eliminar o cambiar objetos puede afectar otros objetos de la base de datos como vistas o procedimientos que dependen de ellos y, en ciertas instancias, pueden “romper” el objeto dependiente. Un ejemplo puede ser si una Vista consulta a una tabla y el nombre de esa tabla cambia. La Vista no funcionará más.

En SQL Server hay muchas maneras de encontrar dependencias de objetos.

1. El procedimiento de sistema sp_depends.

2. Funciones de Administración Dinámica de SQL Server, incluyendo:

   • sys.dm_sql_referencing_entities

   • sys.dm_sql_referenced_entities

3. La característica View Dependencies en SQL Server Management Studio (SSMS).

sp_depends

sp_depends es un procedimiento almacenado del sistema que muestra información acerca de todos los tipos de objetos (por ejemplo, procedimientos, tablas, etc.) que dependen del objeto especificado en el parámetro de entrada, así como todos los objetos de los que el objeto especificado depende.

El procedimiento sp_depends acepta un parámetro, el nombre de un objeto de base de datos. Por ejemplo EXECUTE sp_depends ‘ObjectName’

Abajo están ejemplos, los cuales serán usados en este artículo:

-- New database
CREATE DATABASE TestDB;
GO

USE TestDB
GO

CREATE TABLE UserAddress (
	AddresID INT PRIMARY KEY IDENTITY(1, 1)
	,FirstName VARCHAR(100)
	,Lastname VARCHAR(150)
	,Address VARCHAR(250)
	)
GO

-- New procedure
CREATE PROCEDURE sp_GetUserAddress
AS
BEGIN
	SELECT FirstName
		,Lastname
		,Address
	FROM UserAddress
END
GO

CREATE TABLE Address (
	ID INT NOT NULL IDENTITY(1, 1)
	,City VARCHAR(120)
	,PostalCode INT
	,UserAddressID INT FOREIGN KEY REFERENCES UserAddress(AddresID)
	)
GO

-- New View
CREATE VIEW v_Address
AS
SELECT ID
	,City
	,PostalCode
	,UserAddressID
FROM dbo.Address
GO

CREATE PROCEDURE sp_GetUserCity
AS
BEGIN
	SELECT UserAddress.FirstName
		,UserAddress.Lastname
		,Address.City
	FROM UserAddress
	INNER JOIN Address ON UserAddress.AddresID = Address.UserAddressID
END
GO

-- New Trigger
CREATE TRIGGER trgAfterInsert ON [dbo].[UserAddress]
FOR INSERT
AS
PRINT 'Data entered successfully'
GO

Corramos estos scripts de arriba para crear los objetos de prueba, luego ejecute el siguiente SQL.

EXECUTE sp_depends 'UserAddress'

El siguiente resultado será:

• name – nombre del objeto dependiente.

• type – tipo de objeto dependiente (por ejemplo, tabla).

Si un procedimiento almacenado es especificado como un argumento válido en sp_depends, entonces el nombre de la tabla y los nombres de las columnas de los que el procedimiento depende serán mostrados.

Veamos cómo se ve esto con sp_GetUserAddress

EXECUTE sp_depends 'sp_GetUserAddress'

El siguiente resultado será:

• name – nombre del objeto dependiente.

• type – tipo del objeto dependiente (por ejemplo, tabla).

• updated – si el objeto está actualizado o no.

• selected – el objeto es usado en la sentencia SELECT.

• column – la columna en la que existe la dependencia.

sp_depends no muestra desencadenadores.

Para ilustrar esto, ejecute el siguiente código en la ventana de consultas:

CREATE TRIGGER trgAfterInsert ON [dbo].[UserAddress]
FOR INSERT
AS
PRINT 'Data entered successfully'
GO

Ahora, ejecute sp_depends sobre la tabla UserAddress, trgAfterInsert no aparecerá en la tabla Results:

sp_depends en algunos casos no reporta las dependencias correctamente. Veamos la situación cuando un objeto (por ejemplo, UserAddress) sobre el que dependen otros objetos (por ejemplo, sp_GetUserAddress) es eliminado y recreado. Cuando sp_depends es ejecutado usando EXECUTE sp_depends ‘sp_GetUserAddress’ o EXECUTE sp_depends ‘UserAddress’, el siguiente mensaje aparecerá:

“Object does not reference any object, and no objects reference it.”

Desafortunadamente, sp_dependes está en el camino de la deprecación y será removido de futuras versiones de SQL Server. Pero usted puede usar sys.dm_sql_referencing_entities y sys.dm_sql_referenced_entities en su lugar.

sys.dm_sql_referencing_entities

Esta función retorna todos los objetos desde la base de datos actual, la cual depende del objeto que está especificado como argumento.

Escriba la siguiente consulta en la ventana de consultas:

SELECT referencing_schema_name
	,referencing_entity_name
FROM sys.dm_sql_referencing_entities('dbo.UserAddress', 'Object')

El resultado será:

referencing_schema_name – esquema de la entidad de referencia.

referencing_entity_name – nombre del objeto de referencia.

Más información acerca de los conjuntos de resultado puede ser encontrada en este enlace.

sys.dm_sql_referenced_entities

Esta función de sistema retorna todos los objetos desde la base de datos actual de la cual dependen los objetos especificados.

Ingrese el siguiente código en la ventana de consultas:

SELECT referenced_entity_name
	,referenced_minor_name
FROM sys.dm_sql_referenced_entities('dbo.sp_GetUserAddress', 'Object')

El resultado será mostrado:

referenced_entity_name – nombre del objeto referenciado.

referenced_minor_name – nombre de la columna de la entidad referenciada.

Para información detallada acerca de los conjuntos de resultados, por favor visite este enlace.

De referencia versus referenciado

Los objetos que aparecen dentro de la expresión SQL son llamados la entidad referenciada, y los objetos que contienen expresiones son llamados la entidad de referencia.

Cuando se usan estas dos funciones, el nombre del esquema (por ejemplo, dbo) debe ser especificado como parte del nombre del objeto:

SELECT referencing_schema_name
	,referencing_entity_name
FROM sys.dm_sql_referencing_entities('dbo.UserAddress', 'Object')

De otra manera, los resultados no serán mostrados. Ejecute la consulte sin el nombre del esquema (dbo):

SELECT referencing_schema_name
	,referencing_entity_name
FROM sys.dm_sql_referencing_entities('UserAddress', 'Object')

El resultado será un conjunto vacío:

Un conjunto de resultados vacío será mostrado bajo estas situaciones:

• Cuando un parámetro no válido es pasado (por ejemplo, ‘dbo.UserAddress’,’NN’ en lugar de dbo.UserAddress’,’Object’).

• Cuando un objeto de sistema es especificado como argumento (por ejemplo, sys.all_columns).

• Cuando el objeto especificado no referencia ningún objeto.

• El objeto especificado no existe en la base de datos actual.

El mensaje 2020

Típicamente, el mensaje 2020 ocurre cuando un objeto de referencia, por ejemplo, un procedimiento, llama al objeto referenciado, por ejemplo, una tabla o una columna de la tabla, que no existe. Por ejemplo, si en la tabla Address se cambia el nombre de la columna City a Town y se ejecuta la consulta SELECT * FROM sys.dm_sql_referenced_entities (‘[dbo].[v_Address]’,’Object’), el mensaje 2020 aparecerá.

Ejecute el siguiente código:

EXEC sys.sp_rename 'dbo.Address.City'
	,'Town'
	,'COLUMN'

SELECT *
FROM sys.dm_sql_referenced_entities('dbo.v_Address', 'OBJECT')

El siguiente mensaje aparecerá:

Msg 207, Level 16, State 1, Procedure v_Address, Line 6
Invalid column name ‘City’.
Msg 2020, Level 16, State 1, Line 3
The dependencies reported for entity «dbo.v_Address» might not include references to all 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.

Resolución de problemas

Para evitar eliminar o modificar objetos que dependen de otro objeto, la vista v_Address debería ser alterada y añadida con la opción WITH SCHEMABINDING:

ALTER VIEW v_Address
	WITH SCHEMABINDING
AS
SELECT ID
	,City
	,PostalCode
	,UserAddressID
FROM dbo.Address

Ahora, cuando se cambie el nombre de la columna en la tabla Address, el siguiente mensaje aparecerá, el cual provee información proactivamente acerca del objeto, y dice que la tabla “City” en este ejemplo es una parte de otro objeto.

Código:

EXEC sys.sp_rename 'dbo.Address.City'
	,'Town'
	,'COLUMN'

Mensaje:

Msg 15336, Level 16, State 1, Procedure sp_rename, Line 501
Object ‘dbo.Address.City’ cannot be renamed because the object participates in enforced dependencies.

Dependencias enlazadas a esquema versus dependencias no enlazadas a esquema

Hay dos tipos de dependencias: enlazadas a esquema y no enlazadas a esquema.

Una Dependencia Enlazada a Esquema (SCHEMABINDING) evita que los objetos referenciados sean alterados o eliminados mientras el objeto de referencia exista.

Una Dependencia No Enlazada a Esquema: No evita que el objeto referenciado sea alterado o eliminado.

Para sys.dm_sql_referenced_entities y sys.dm_sql_referencing_entities la información de dependencia no será mostrada para tablas temporales, procedimiento almacenados temporales u objetos del sistema.

Abajo está un ejemplo de un procedimiento temporal:

CREATE PROCEDURE #sp_tempData
AS
BEGIN
	SELECT AddresID
		,FirstName
		,Lastname
		,Address
	FROM UserAddress
END

Ahora, cuando se ejecuta sys.dm_sql_referencing_entities para la tabla UserAddress, la información acerca del procedimiento #sp_tempData, que depende de UserAddress, no será mostrada en la lista.

Código:

SELECT referencing_schema_name
	,referencing_entity_name
FROM sys.dm_sql_referencing_entities('dbo.UserAddress', 'Object')

Resultado:

Viendo Dependencias

Otra forma de ver dependencias entre objetos es usando la opción View Dependencies desde SSMS. Desde el panel Object Explorer, haga clic derecho en el objeto y, desde el menú contextual, seleccione la opción View Dependencies:

Esto abrirá la ventana Object Dependencies. Por defecto, el botón radial Object that depend on está seleccionado. Este botón radial listará en la sección Dependencies todos los objetos que dependen del objeto seleccionado (por ejemplo, Address):

Si el botón radial Object on which está seleccionado, mostrará en la sección Dependencies todos los objetos de los que depende el objeto seleccionado (por ejemplo, Address):

La sección Selected object consiste en tres campos:

• Name – nnombre del objeto seleccionado desde la lista Dependencies.

• Type – tipo del objeto seleccionado (por ejemplo, tabla).

• Dependency type – dependencia entre dos objetos (enlazadas a esquema y no enlazadas a esquema).

Debajo del campo Type, el tipo Unresolved Entity para ese objeto puede aparecer. Esto pasa cuando el objeto se refiere a un objeto que no existe en la base de datos. Esto es el equivalente al Mensaje 2020 que aparece cuando se usan las funciones sys.dm_sql_referencing_entities o sys.dm_sql_referenced_entities functions:

Visor de dependencias de terceros

ApexSQL Search es un complemento gratis que se integra en SSMS y Visual Studio para objetos SQL y búsqueda de datos de texto, administración de propiedades extendida, renombramiento seguro de objetos y visualización de relaciones.

Este complemento puede ser descargado desde este enlace.

Para ver las dependencias de objetos, haga clic derecho en un objeto (por ejemplo, un procedimiento almacenado) desde Object Explorer, y elija la opción View dependencies desde el menú desplegable de ApexSQL Search:

O seleccione el objeto en Object Explorer desde el menú principal de ApexSQL, elija el menú de ApexSQL Search y desde la lista seleccione la opción View dependencies:

Después de hacer clic, la ventana Dependency viewer aparecerá con el panel Dependencies, el cual muestra todos los objetos que dependen del objeto seleccionado (por ejemplo, UserAddress). Por defecto, este panel aparece en la parte inferior derecha de la ventana Dependency viewer:

Dependency viewer provee una vista gráfica de todas las dependencias entre los objetos en el medio de la ventana Dependency viewer:

Dependencias visuales

Dependency viewer ofrece varias opciones para filtrar, la apariencia y la manipulación de objetos.

En el panel Object filter, los tipos de objetos (por ejemplo, vista) que serán mostrados en el gráfico de dependencia pueden ser especificados:

En el panel Object browser, objetos específicos pueden ser seleccionados que serán mostrados u omitidos desde el gráfico de dependencia:

En el panel Dependencies, la cadena completa de dependencias para el objeto seleccionado en el gráfico de dependencia (por ejemplo, Address) puede ser mostrada. Children indica el objeto que depende del objeto seleccionado (de referencia) y Parents muestra los objetos de los que depende el objeto seleccionado (referenciado):

La opción Layout debajo de la cinta Display, ofrece diferentes opciones para la organización visual y la presentación:

Por ejemplo, la opción Hierarchical organizará los objetos basada en la “generación”, por ejemplo padres, hijos, descendientes, de manera que el padre (de referencia) estará en la cúspide (UserAddress) y los objetos descendientes (referenciados) estarán debajo de ellos (sp_GetUserCity). Los objetos de la misma generación estarán en el mismo nivel horizontal:

Usando esta opción, puede ser fácilmente determinado cuándos objetos dependen del objeto específico, y una determinación puede ser hecha una vez que es seguro eliminarla sin romper relaciones.

Las etiquetas que aparecen en el gráfico de dependencia arriba (por ejemplo, Select, Trigger, etc.) describen el tipo de relación, por ejemplo, Select, DRI.

Por ejemplo, hay una etiqueta “Select” en la relación entre la tabla UserAddress y el procedimiento sp_GetUserAddress. Esto es porque sp_GetUserAddress contiene una sentencia SELECT referenciando la tabla UserAddress.

En la relación entre dos otras tablas, vemos la etiqueta “DRI”. Integridad Referencial Declarativa (Declarative Referential Integrity, existe sólo entre tablas SQL e indica, en este caso, que en el objeto (FOREIGN KEY – UserAddressID) existe en la tabla Address que referencia al objeto desde la tabla UserAddress (PRIMARY KEY – AddressID):

La opción Type permite elegir entre qué tipo de relación será incluida en el gráfico de dependencia, por ejemplo, Children only (sólo hijos):

Por ejemplo, la opción Parents and descendants mostrará todas las dependencias desde el objeto seleccionado (UserAddress). Esta opción mostrará la cadena completa de dependencia incluyendo padres, hijos y descendientes:

Más información acerca de las opciones Layout y Type puede ser encontrada en este enlace.

Vea también:

• sys.dm_sql_referencing_entities (Transact-SQL)
• sys.dm_sql_referenced_entities (Transact-SQL)
• sp_depends (Transact-SQL)