Cómo obtener dependencias de objetos en SQL Server

Los lenguajes de programación tienen objetos que dependen de otro objeto. Un principio bastante similar se aplica a una base de datos. Hay varios tipos de dependencias de objetos en una base de datos, incluidas las relaciones entre tablas, claves foráneas primarias, restricciones y asociaciones de esquemas. Por lo tanto, las dependencias de objetos se vuelven críticas en una base de datos en crecimiento y deben manejarse con precaución.

Por ejemplo, una tabla con una clave externa que depende de la clave principal de otra tabla. Una definición de vista depende de una tabla. Procedimiento almacenado dependiente de otro procedimiento almacenado para obtener los datos. Una tabla puede depender de un índice para una recuperación de filas más rápida.

Pueden producirse efectos secundarios inesperados si se elimina un objeto en SQL Server sin analizar sus dependencias. Definitivamente útil para descubrirlos. objetos de los que dependen [object] Y objetos en los que [object] depende. Para asegurarse de que eliminar un objeto no haga que el diseño de la base de datos sea inconsistente.

Ejecutamos el siguiente script para crear objetos que sean interdependientes entre sí, como se muestra en la siguiente imagen. Usando estos objetos, intentaremos encontrar su dependencia usando la consulta y la vista gráfica de SQL Server.

USE tempdb;
GO

--Dropping existing objects in perticular order to avoid depedencies error
IF OBJECT_ID('EmpInfo','V') IS NOT NULL DROP VIEW EmpInfo
IF OBJECT_ID('Emp','U')     IS NOT NULL DROP TABLE Emp
IF OBJECT_ID('Dept','U')    IS NOT NULL DROP TABLE Dept
IF OBJECT_ID('Dept_A','P')  IS NOT NULL DROP PROCEDURE Dept_A
IF OBJECT_ID('Dept_B','P')  IS NOT NULL DROP PROCEDURE Dept_B

CREATE TABLE Dept (Id   INT PRIMARY KEY
                  ,Name VARCHAR(50));

--Foreign key Reference
CREATE TABLE Emp (
                  Id   INT         PRIMARY KEY
                 ,Name VARCHAR(20)
		 ,DeptId	   INT FOREIGN KEY REFERENCES Dept(Id)
                 );
GO

--View-dependent on Emp table.
CREATE VIEW EmpInfo  AS 
SELECT Id, Name FROM Emp;	
GO

--Stored Proc dependent on Dept & Emp table.
CREATE PROCEDURE Dept_A AS 
BEGIN
	SELECT * FROM Dept INNER JOIN Emp 
	ON Dept.Id = Emp.DeptId;
END
GO

--Stored Proc dependent on Dept_A another Stored Proc.
CREATE PROCEDURE Dept_B AS 
BEGIN
	EXEC Dept_A;
END

La consulta anterior crea los siguientes objetos y su dependencia como se muestra a continuación.

Encuentre dependencias de objetos en SQL Server Management Studio (SSMS)

Vaya al Explorador de objetos en SSMS o use el método abreviado de teclado F8 para abrir el Explorador de objetos.

Expanda el árbol de nombres de la base de datos y navegue hasta el nombre del objeto.

Haga clic derecho en el nombre del objeto resaltado (tabla/vista/procedimiento almacenado) y seleccione Ver dependencias.

Usando los pasos anteriores podemos encontrar (tabla/vista/procedimiento almacenado/función/disparador) y otras dependencias de objetos usando una vista de árbol gráfica.

Encuentre dependencias de objetos usando DMF (Funciones de administración dinámica)

Usando la siguiente función de gestión dinámica podemos encontrar las dependencias de los objetos en SQL Server. Es importante señalar que en estos DMF es obligatorio especificar el nombre del esquema en el primer parámetro con el nombre del objeto. Por ejemplo, use dbo.Emp en lugar de Emp.

sys.dm_sql_referenced_entities

sys.dm_sql_referencing_entities

Antes de aprender más sobre estos DMF, es importante comprender los siguientes conceptos.

Entidad de referencia

Cuando se muestra un objeto dentro de una instrucción SQL, el objeto se clasifica como una entidad de referencia. En nuestro ejemplo, la tabla Dept es Entity Referenced como se hace referencia en la vista Dept_A. Usando sys.dm_sql_referenced_entities DMF podemos encontrar entidades a las que se hace referencia en un objeto.

SELECT * FROM sys.dm_sql_referenced_entities('dbo.Emp','Object');
SELECT * FROM sys.dm_sql_referenced_entities('dbo.Dept','Object');
SELECT * FROM sys.dm_sql_referenced_entities('dbo.EmpInfo','Object');
SELECT * FROM sys.dm_sql_referenced_entities('dbo.Dept_A','Object');
SELECT * FROM sys.dm_sql_referenced_entities('dbo.Dept_B','Object');

En la siguiente figura lo podemos ver EMP Y DEPARTAMENTO aparecer dentro Departamento_A procedimiento. Son por tanto entidades referenciadas.

Entidad de referencia

La instrucción SQL que contiene una referencia a otro objeto se clasifica como una entidad de referencia. es nuestro caso, Departamento_A view se refiere a la entidad en la que aparece el objeto de la tabla Dept. sys.dm_sql_referencing_entities DMF podemos encontrar Entidad referenciada.

SELECT * FROM sys.dm_sql_referencing_entities('dbo.Emp','Object');
SELECT * FROM sys.dm_sql_referencing_entities('dbo.Dept','Object');
SELECT * FROM sys.dm_sql_referencing_entities('dbo.EmpInfo','Object');
SELECT * FROM sys.dm_sql_referencing_entities('dbo.Dept_A','Object');
SELECT * FROM sys.dm_sql_referencing_entities('dbo.Dept_B','Object');

Además, en la siguiente ilustración podemos verlo Departamento_A procedimiento y la vista EmpInfo contiene una referencia a Emp mesa.

Encuentra dependencias de objetos usando sp_depends

Aunque sp_depende es una característica obsoleta, todavía podemos usarla. Microsoft planea eliminar este procedimiento almacenado del sistema en versiones futuras, por lo que no recomendamos usarlo para casos de uso de producción. Sin embargo, hemos observado que produce resultados incorrectos en algunas adicciones complicadas. Yo recomendaría usar sys.dm_sql_referencing_entities Y sys.dm_sql_referenced_entities siguiendo los consejos de los documentos de Microsoft.

Use tempdb;
Go

EXEC sp_depends @objname = 'Dept';

De Documentos de Microsoft

Esta característica está en modo de mantenimiento y puede eliminarse en una versión futura de Microsoft SQL Server. Evite usar esta función en nuevos trabajos de desarrollo y planee modificar las aplicaciones que actualmente usan esta función.

Resumen

Sobre todo, podemos aprender cómo encontrar dependencias de objetos en SQL Server utilizando métodos gráficos y consultas. Además, esto puede ayudarnos a estudiar la dependencia del objeto antes de tomar la decisión de eliminarlo de la base de datos. Si le gusta esta publicación, puede leer las publicaciones de la base de datos de recetas técnicas.