El eliminar o cambiar objetos puede llegar a afectar a otros objetos de la base de datos como vistas o procedimientos que dependen de ellos y en ciertos casos pueden “romper” el objeto dependiente. Por ejemplo, puede ser que si una vista de consulta a una tabla existe y el nombre de esa tabla cambia, la vista ya no funcionará.
Para poder comprender las interdependencias acerca de nuestra base de datos, es muy útil ver y analizar estas dependencias en un árbol de dependencias de SQL Server y en última instancia, incluso poder crear un diagrama de dependencias de SQL que muestre visualmente las relaciones jerárquicas.
En SQL Server hay varias formas de encontrar dependencias de objetos y crear un rastreador de dependencias de SQL.
- El procedimiento almacenado del sistema sp_depends.
- Funciones de gestión dinámica de SQL Server incluidas
- sys.dm_sql_referencing_entities
- sys.dm_sql_referenced_entities
- La función para ver dependencias en SQL Server Management Studio (SSMS)
sp_depends
sp_depends es un procedimiento almacenado del sistema que nos muestra información sobre todos los tipos de objetos (por ejemplo, procedimientos, tablas, etc.) que dependen del objeto especificado en el parámetro de entrada, al igual que como de todos los objetos de los que depende el objeto especificado.
El procedimiento sp_depends acepta un parámetro que es el nombre de un objeto de base de datos. P.ej. EJECUTAR sp_depends ‘ObjectName’
A continuación se presentan unos ejemplos, que se utilizarán en este artículo:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 |
-- 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 |
Ejecutemos estos scripts anteriores para poder crear los objetos de prueba y luego ejecutemos el siguiente SQL.
|
1 |
EXECUTE sp_depends 'UserAddress' |
El siguiente resultado será:
name | type | |
1 | dbo.sp_GetUserAddress | stored procedure |
2 | dbo.sp_GetUserCity | stored procedure |
- nombre – nombre del objeto dependiente
- tipo – tipo de objeto dependiente (por ejemplo, tabla)
Si se especifica un procedimiento almacenado como un valor de argumento en sp_depends, se mostrarán un nombre de la tabla y los nombres de columna de los que depende el procedimiento.
Veamos cómo se vería esto con sp_GetUserAddress
|
1 |
EXECUTE sp_depends 'sp_GetUserAddress' |
El siguiente resultado será:
name | type | updated | selected | column | |
1 | dbo.UserAddress | user table | no | yes | FirstName |
2 | dbo.UserAddress | user table | no | yes | LastName |
3 | dbo.UserAddress | user table | no | yes | Addresss |
- nombre/name – nombre del objeto dependiente
- tipo/type – tipo de objeto dependet (por ejemplo, tabla)
- actualizado/updated – si el objeto se actualiza o no
- elegido/selected – el objeto se usa en la instrucción SELECT
- columna/column – columna en la que existe la dependencia
sp_depends no muestra los desencadenadores.
Para poder ilustrar esto, ejecute el siguiente código en la ventana de consulta:
|
1 2 3 4 5 |
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 de resultados:
name | type | |
1 | dbo.sp_GetUserAddress | stored procedure |
2 | dbo.sp_GetUserCity | stored procedure |
sp_dependes en algunas ocasiones no podrá informar las dependencias correctamente. Veamos una situación cuando un objeto (por ejemplo, UserAddress) del que depende otro objeto (por ejemplo, sp_GetUserAddress) se elimina y se vuelve a crear. Cuando sp_dependes se ejecuta usando EXECUTE sp_depends ‘sp_GetUserAddress’ o EXECUTE sp_depends ‘UserAddress’, nos aparecerá el siguiente mensaje:
“El objeto no hace referencia a ningún objeto, y ningún objeto hace referencia a él”.
Lastimosamente, sp_dependes se encuentra en una ruta de desaprobación y se eliminará su uso en futuras versiones de SQL Server. Pero en su lugar puedes usar sys.dm_sql_referencing_entities y sys.dm_sql_referenced_entities para reemplazarlo.
sys.dm_sql_referencing_entities
Esta función devuelve todos los objetos de la base de datos actual que dependen del objeto que se especifica como un argumento.
Escriba lo siguiente en la ventana de consulta:
|
1 2 3 |
SELECT referencing_schema_name ,referencing_entity_name FROM sys.dm_sql_referencing_entities('dbo.UserAddress', 'Object') |
El resultado será:
referencing_schema_name | referencing_entity_name | |
1 | dbo | sp_GetUserAddress |
2 | dbo | sp_GetUserCity |
referencing_schema_name – esquema de la entidad de referencia
remitencing_entity_name – nombre del objeto de referencia
Puede hallar más información sobre los conjuntos de resultados haciendo clic en el enlace.
sys.dm_sql_referenced_entities
Esta función del sistema nos devuelve todos los objetos de la base de datos actual de la que depende el objeto especificado.
Ingrese el siguiente código en la ventana de consulta:
|
1 2 3 |
SELECT referenced_entity_name ,referenced_minor_name FROM sys.dm_sql_referenced_entities('dbo.sp_GetUserAddress', 'Object') |
Se mostrará el siguiente resultado:
referenced_entity_name | referenced_minor_name | |
1 | UserAddress | NULL |
2 | UserAddress | FirstName |
3 | UserAddress | Lastname |
4 | UserAddress | Address |
referenced_entity_name – nombre del objeto al que se hace referencia
referenced_minor_name – nombre de la columna de la entidad a la que se hace referencia
Para poder obtener información detallada sobre los conjuntos de resultados, visite la página en este enlace.
Referenciar vs referenciado
Los objetos que muestran dentro de la expresión SQL se denominan entidad referenciada y los objetos que contienen expresiones se denominan entidad referenciadora:
Al tratar de usar estas dos funciones, el nombre del esquema (por ejemplo, dbo) debe especificarse como parte del nombre del objeto:
|
1 2 3 |
SELECT referencing_schema_name ,referencing_entity_name FROM sys.dm_sql_referencing_entities('dbo.UserAddress', 'Object') |
De otro modo no se mostrarán los resultados. Ejecuta la consulta sin esquema (dbo):
|
1 2 3 |
SELECT referencing_schema_name ,referencing_entity_name FROM sys.dm_sql_referencing_entities('UserAddress', 'Object') |
El resultado será un conjunto vacío:
referencing_schema_name | referencing_entity_name | |
Se expondrá un conjunto de resultados vacíos en estas situaciones:
- Cuando se pasa un parámetro no válido (por ejemplo, ‘dbo.UserAddress’, ’NN’ en vez de dbo.UserAddress ’,’ Object ’)
- Cuando un objeto del sistema se especifica como argumento (por ejemplo, sys.all_columns)
- Cuando el objeto especificado no hace referencia a ningún objeto.
- El objeto especificado no existe en la base de datos actual.
El mensaje 2020
Generalmente, el mensaje 2020 aparece cuando un objeto de referencia, por ejemplo: procedimiento, llama a un objeto referenciado, por ejemplo: Tabla o una columna de la tabla que no existe. Por ejemplo, si en la tabla de direcciones, cambie el nombre de la columna Ciudad a nombre pueblo y ejecute la consulta SELECT * FROM sys.dm_sql_referenced_entities (‘[dbo]. [V_Address]’, ’Object’), aparecerá el mensaje 2020.
Ejecuta el siguiente código:
|
1 2 3 4 5 6 |
EXEC sys.sp_rename 'dbo.Address.City' ,'Town' ,'COLUMN' SELECT * FROM sys.dm_sql_referenced_entities('dbo.v_Address', 'OBJECT') |
Aparecerá el siguiente mensaje:
Msg 207, Nivel 16, Estado 1, Procedimiento v_Dirección, Línea 6
Nombre de columna no válido “Ciudad”.
Msg 2020, Nivel 16, Estado 1, Línea 3 Las dependencias informadas para la entidad “dbo.v_Address” podrían no incluir referencias a todas las columnas. Esto se produce debido a que la entidad hace referencia a un objeto que no existe o debido a un error en una o más declaraciones en la entidad. Previo a volver a ejecutar la consulta, asegúrese de que no haya errores en la entidad y que todos los objetos a los que hace referencia la entidad existen.
Solución de problemas
Para poder evitar que se caigan o modifiquen los objetos, que dependen de otro objeto, la vista v_Address debe de modificarse y agregarse la opción WITH SCHEMABINDING:
|
1 2 3 4 5 6 7 8 |
ALTER VIEW v_Address WITH SCHEMABINDING AS SELECT ID ,City ,PostalCode ,UserAddressID FROM dbo.Address |
Entonces, al cambiar el nombre de la columna en la tabla de direcciones, aparecerá el siguiente mensaje, que nos brinda información de manera proactiva de que el objeto, la tabla “Ciudad” en este ejemplo, es parte de otro objeto.
Código:
|
1 2 3 |
EXEC sys.sp_rename 'dbo.Address.City' ,'Town' ,'COLUMN' |
Mensaje:
Msg 15336, Nivel 16, Estado 1, Procedimiento sp_rename, Línea 501
El objeto ‘dbo.Address.City’ no puede ser renombrado porque el objeto participa en dependencias forzadas.
Esquema enlazado vs esquema no enlazado
Hay dos tipos de dependencias: dependencias vinculadas a esquema y dependencias no vinculadas a esquema.
Una dependencia vinculada a un esquema (SCHEMABINDING) que evita que los objetos referenciados se alteren o se eliminen mientras exista el objeto de referencia.
Una dependencia no vinculada a esquema: no impide que el objeto al que se hace referencia se modifique o se caiga.
Para sys.dm_sql_referenced_entities y sys.dm_sql_referencing_entities, la información de dependencia no se mostrará para tablas temporales, procedimientos almacenados temporales u objetos del sistema.
A continuación se muestra un ejemplo de un procedimiento temporal:
|
1 2 3 4 5 6 7 8 9 |
CREATE PROCEDURE #sp_tempData AS BEGIN SELECT AddresID ,FirstName ,Lastname ,Address FROM UserAddress END |
Entonces, al ejecutar sys.dm_sql_referencing_entities para la tabla UserAddress, la información sobre el procedimiento #sp_tempData que depende de UserAddress no se mostrará en la lista.
Código:
|
1 2 3 |
SELECT referencing_schema_name ,referencing_entity_name FROM sys.dm_sql_referencing_entities('dbo.UserAddress', 'Object') |
Resultado:
referencing_schema_name | referencing_entity_name | |
1 | dbo | sp_GetUserAddress |
2 | dbo | sp_GetUserCity |
Visualización de dependencias
Otra manera de ver las dependencias entre objetos, pero para poder crear un rastreador de dependencia visual de SQL, es a través del uso de la opción Ver dependencias de SSMS. En el panel del Explorador de objetos, haga clic con el botón derecho en el objeto y, en el menú contextual, seleccione la opción Ver dependencias:
Esto hará que se despliegue la ventana Dependencias de objetos. De forma predeterminada, se selecciona el objeto que depende del botón de opción. Este botón de opción desplegara en la sección Dependencias todos los objetos que dependen del objeto seleccionado (por ejemplo, Dirección):
Si seleccionó el Objeto en el que el botón de opción, mostrará en la sección Dependencias todos los objetos de los que depende el objeto seleccionado (por ejemplo, Dirección):
La sección Objeto seleccionado consta de tres campos:
- Nombre – nombre del objeto seleccionado de la lista de Dependencias
- Tipo – tipo del objeto seleccionado (por ejemplo, mesa)
- Tipo de dependencia – dependencia entre dos objetos (vinculados a esquema, no vinculados a esquema).
Bajo el campo Tipo, puede mostrarse el tipo de entidad sin resolver para el objeto. Esto suele suceder cuando los objetos se refieren a un objeto que no existe en la base de datos. Esto es igual al mensaje Msg 2020 que aparece cuando se usan las funciones sys.dm_sql_referencing_entities o sys.dm_sql_referenced_entities:
Este rastreador de dependencias de SQL es una especie de diagrama de dependencias de SQL, ya que no muestra relaciones cruzadas ni ofrece muchas funciones de valor agregado, pero le brinda una vista previa rápida de las dependencias en la jerarquía que contiene un objeto en particular.
Alternativas
ApexSQL Analyze es una herramienta de terceros que puede analizar las dependencias de objetos de la base de datos de SQL Server y el impacto de posibles eliminaciones en su base de datos de SQL y para crear un diagrama de dependencia de SQL. Se determina las interrelaciones de objetos dentro de la base de datos y otorga la personalización de la apariencia del diagrama de dependencia SQL resultante.
Esta herramienta se puede descargar desde las descargas de herramientas SQL.
Para poder ver las dependencias de los objetos, en la pestaña Inicio, haga clic en el botón Nuevo, en la ventana Conexión a SQL Server y elija la instancia de SQL Server, escoja el tipo de autenticación y después de seleccionar la base de datos deseada en el cuadro desplegable Base de datos, haga clic en Botón de conexión:
Aparecerá la ventana del visor de dependencias:
Con el panel de dependencias, que nos muestra todos los objetos que dependen del objeto seleccionado (por ejemplo, UserAddress), este panel aparece de forma predeterminada en el lado derecho de la ventana del Visor de dependencias:
El Visor de dependencias nos proporciona una vista gráfica de todas las dependencias entre objetos en medio de la ventana del Visor de dependencias:
Dependencias visuales
El visor de dependencias nos ofrece varias opciones para poder filtrar, aparentar y manipular objetos.
En el panel del navegador de objetos, se pueden especificar todos los tipos de objetos (por ejemplo, la vista) que se mostrarán en el gráfico de dependencia:
Adicionalmente, en el panel del navegador de objetos, se pueden elegir objetos específicos que se mostrarán u omitirán en el gráfico de dependencia:
Se puede descubrir en el panel Dependencias, la cadena de dependencia completa para el objeto seleccionado en el gráfico de dependencia (por ejemplo, Dirección). Las referencias indican el objeto que depende del objeto seleccionado (también conocido como referencia) y Referenciado muestra los objetos de los que depende el objeto seleccionado (también conocido como referenciado):
Aparte de eso, la cadena de dependencias se puede revisar seleccionando un objeto en el gráfico de dependencias (por ejemplo, Dirección), haga clic con el botón derecho y en el menú contextual, debajo del submenú Seleccionar, elija el comando Referencias de objetos u Objetos referenciados:
La opción Diseño en la cinta Mostrar nos ofrece distintas opciones para la organización visual y la visualización:
Como por ejemplo, la opción Ortogonal trata de organizar los objetos en los diagramas para que estén en ángulo recto entre sí. Es muy útil para la identificación rápida de todos los objetos relacionados con un objeto dado (es decir, tanto los que dependen de él como de los que depende):
Utilizando esta opción, se puede determinar de una forma fácil cuántos objetos dependen de un objeto específico y se puede determinar si es seguro eliminarlo sin romper relaciones
La opción Mostrar columnas nos muestra columnas con tipos de datos de las tablas y los objetos de vista:
Adicionalmente, la definición de un objeto se puede revisar fácilmente seleccionando el objeto deseado (por ejemplo, el archivo con el nombre del usuario), haga clic con el botón derecho y en el menú contextual, seleccione el comando Mostrar secuencia de comandos:
La ventana del script se desplegará con la definición del objeto seleccionado:
En este artículo nos enfocamos en analizar las interdependencias de los objetos de SQL Server, cómo encontrarlos utilizando SQL, SSMS y una solución de terceros, y finalmente cómo poder crear un diagrama de dependencia SQL a partir de los resultados. ¡Feliz diagramación!
Actualmente trabajando para ApexSQL LLC como un Ingeniero de Ventas de Software, él está ayudando a los clientes con problemas técnicos y hace aseguramiento de calidad para los complementos ApexSQL Complete, ApexSQL Refactor y ApexSQL Search.
Ver todas las entradas de Marko Zivkovic