Marko Zivkovic

Como crear un diagrama de dependencia SQL en SQL Server

July 5, 2019 by

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.

  1. El procedimiento almacenado del sistema sp_depends.
  2. Funciones de gestión dinámica de SQL Server incluidas
  • sys.dm_sql_referencing_entities
  • sys.dm_sql_referenced_entities
  1. 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:

Ejecutemos estos scripts anteriores para poder crear los objetos de prueba y luego ejecutemos el siguiente SQL.

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

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:

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:

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:

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:

De otro modo no se mostrarán los resultados. Ejecuta la consulta sin esquema (dbo):

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:

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:

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:

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:

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:

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!

Marko Zivkovic

Marko Zivkovic

Marko es un ingeniero mecánico a quien le gusta jugar basketball, football (table-soccer) y escuchar música rock. Está interesado en código SQL, desarrollo PHP y técnicas en HTML y CSS.
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
Marko Zivkovic
Relaciones y dependencias

Acerca de Marko Zivkovic

Marko es un ingeniero mecánico a quien le gusta jugar basketball, football (table-soccer) y escuchar música rock. Está interesado en código SQL, desarrollo PHP y técnicas en HTML y CSS. 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

832 Views