As a database consultant, one of the challenges I often face is analyzing the performance of SQL Server instances that I have never seen before. This can be a difficult task, especially when there is no documentation or the existing documentation is outdated. In order to fix any issues, it is crucial to understand the database structure and the relationships between objects. One way to do this is by using SQL Server’s built-in functionality to view dependencies.

SQL Server provides two types of dependencies: hard and soft. Hard dependencies are those that cannot be violated without causing integrity issues, such as foreign keys between tables or views created with the SCHEMABINDING parameter. Soft dependencies, on the other hand, do not impede object deletion.

To view hard dependencies, you can use the sys.foreign_keys system view. For example, to find all foreign keys that reference a specific table, you can execute the following query:

SELECT OBJECT_NAME(f.parent_object_id), OBJECT_NAME(f.referenced_object_id)
FROM sys.foreign_keys f
WHERE f.referenced_object_id = OBJECT_ID('dbo.tableA')
AND f.parent_object_id != referenced_object_id

Soft dependencies can be found using system views such as sys.sql_expression_dependencies, sys.sql_dependencies, and sys.sql_expression_dependencies. These views provide information about script dependencies and can help identify non-existent links. For example, you can use the sys.sql_expression_dependencies view to search for non-existent objects inside functions, views, and stored procedures:

SELECT
SCHEMA_NAME(o.[schema_id]),
o.name,
d.referenced_database_name,
d.referenced_schema_name,
d.referenced_entity_name
FROM sys.sql_expression_dependencies d
JOIN sys.objects o ON d.referencing_id = o.[object_id]
WHERE d.is_ambiguous = 0
AND d.referenced_id IS NULL
AND d.referenced_server_name IS NULL
AND CASE d.referenced_class
WHEN 1
THEN OBJECT_ID(
ISNULL(QUOTENAME(d.referenced_database_name), DB_NAME()) + '.' +
ISNULL(QUOTENAME(d.referenced_schema_name), SCHEMA_NAME()) + '.' +
QUOTENAME(d.referenced_entity_name))
WHEN 6
THEN TYPE_ID(
ISNULL(d.referenced_schema_name, SCHEMA_NAME()) + '.' + d.referenced_entity_name)
WHEN 10
THEN (
SELECT 1 FROM sys.xml_schema_collections x
WHERE x.name = d.referenced_entity_name
AND x.[schema_id] = ISNULL(SCHEMA_ID(d.referenced_schema_name), SCHEMA_ID())
)
END IS NULL

When it comes to finding dependencies for a specific table, SQL Server does not provide a simple query to retrieve the hierarchy of dependencies. However, you can use a combination of system views and temporary tables to achieve this. For example, you can use the sys.sql_expression_dependencies view to find places where a table is used:

SET NOCOUNT ON;
DECLARE @obj_id INT = OBJECT_ID('Person.Person');
IF OBJECT_ID('tempdb.dbo.#h') IS NOT NULL DROP TABLE #h
CREATE TABLE #h (
obj_id INT NULL,
obj_name SYSNAME,
obj_schema SYSNAME NULL,
obj_type CHAR(5) NULL
);
INSERT INTO #h
SELECT
s.referencing_id,
COALESCE(t.name, o.name),
SCHEMA_NAME(o.[schema_id]),
CASE s.referencing_class
WHEN 1 THEN o.[type]
WHEN 7 THEN 'U'
WHEN 9 THEN 'U'
WHEN 12 THEN 'DDLTR'
END
FROM sys.sql_expression_dependencies s
LEFT JOIN sys.objects o ON o.[object_id] = s.referencing_id
AND o.[type] NOT IN ('D', 'C')
LEFT JOIN sys.triggers t ON t.[object_id] = s.referencing_id
AND t.parent_class = 0
AND s.referencing_class = 12
WHERE (o.[object_id] IS NOT NULL OR t.[object_id] IS NOT NULL)
AND s.referenced_server_name IS NULL
AND (
(s.referenced_id IS NOT NULL AND s.referenced_id = @obj_id)
OR
(s.referenced_id IS NULL
AND OBJECT_ID(
QUOTENAME(ISNULL(s.referenced_schema_name, SCHEMA_NAME())) + '.' +
QUOTENAME(s.referenced_entity_name)
) = @obj_id)
)

By using these techniques, you can gain a better understanding of the dependencies within your SQL Server database. However, SQL Server Management Studio (SSMS) may not always provide an efficient and comprehensive solution for viewing dependencies. In such cases, third-party tools like dbForge Studio for SQL Server can be a valuable alternative. These tools offer faster and more accurate dependency analysis, making it easier to navigate and understand the relationships between objects.

In conclusion, understanding SQL Server dependencies is crucial for effective database management and performance optimization. By utilizing the built-in functionality of SQL Server and third-party tools, you can simplify the process of analyzing and resolving dependencies, leading to a more efficient and reliable database system.