SQL SERVER – View Dependencies on SQL Server: Hard & Soft Way

As an independent database consultant, I often face a challenge when analyzing the performance of SQL Server instances, which I see for the first time in my life. In many cases, this may seem a difficult task, because in order to fix something you must first understand the database structure. A great many of the companies do not document their database. And even if documentation exists, it is rarely updated. As a result, we have the following situation – documentation is either obsolete, or does not reflect the actual state of things. Let understand SQL SERVER – View Dependencies.

It stands to reason that, in this case I have to deal with a database structure by myself, meditatively analyzing each object… Step by step. Investigating database structure is much easier with ER diagrams that allow to see the relationships between tables. However, things are not all that good when it comes to the analysis of script objects and their dependencies.

In fact, and what options do we have if we want to see the relationships between objects?

Of course, the most obvious solution is to use the built-in functionality of SQL Server Management Studio to searching dependencies. After all, what could be easier…open SSMS and execute View Dependencies. But as my long-standing practice showed, this SSMS functionality runs very slowly (especially on large databases) and does not always show all the dependencies between objects.

Again, no one stops me from writing my own query for searching dependencies. However, this requires a sound experience in working with metadata.

And now some theory… and a lot of scripts.

In the context of SQL Server, there are two types of dependencies: hard and soft. The main difference between them – the former exclude integrity violation.

Foreign keys between tables, a custom function inside the COMPUTED column, a view created with the SCHEMABINDING parameter — this is a noninclusive list of hard dependencies.

CREATE TABLE dbo.tableA (ID INT PRIMARY KEY)
GO
CREATE TABLE dbo.tableB (ID INT PRIMARY KEY, ParentID INT)
GO
ALTER TABLE dbo.tableB ADD FOREIGN KEY (ParentID) REFERENCES dbo.tableA(ID)
GO
DROP TABLE dbo.tableA

We cannot delete table A without first deleting a foreign key:

Msg 3726, Level 16, State 1, Line 7
Could not drop object ‘dbo.tableA’ because it is referenced by a FOREIGN KEY constraint.

This is a hard dependency, which can be easily found in metadata:

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

Let’s complicate the example:

CREATE TABLE dbo.Table1(
a INT PRIMARY KEY
)
GO
CREATE TABLE dbo.Table2(
a INT PRIMARY KEY,
CONSTRAINT FK_Table2_Table1 FOREIGN KEY (a) REFERENCES dbo.Table1 (a)
)
GO
CREATE TABLE dbo.Table3(
a INT PRIMARY KEY,
CONSTRAINT FK_Table3_Table2 FOREIGN KEY (a) REFERENCES dbo.Table2 (a)
)
GO
CREATE TABLE dbo.Table4(
a INT PRIMARY KEY,
CONSTRAINT FK_Table4_Table2 FOREIGN KEY (a) REFERENCES dbo.Table2 (a)
)
GO
CREATE TABLE dbo.Table5(
a INT PRIMARY KEY,
CONSTRAINT FK_Table5_Table3 FOREIGN KEY (a) REFERENCES dbo.Table3 (a)
)
GO
CREATE TABLE dbo.Table6(
a INT PRIMARY KEY,
CONSTRAINT FK_Table6_Table3 FOREIGN KEY (a) REFERENCES dbo.Table3 (a)
)
GO

and find child objects for Table1:

DECLARE @t TABLE ([object_id] INT PRIMARY KEY)
INSERT INTO @t ([object_id]) VALUES(OBJECT_ID('dbo.Table1'))
DECLARE @rows INT = 1
WHILE @rows > 0 BEGIN
SET @rows = 0
INSERT INTO @tables
SELECT f.parent_object_id
FROM @t t
JOIN sys.foreign_keys f ON f.referenced_object_id = t.[object_id]
WHERE NOT EXISTS(
SELECT 1
FROM @t t2
WHERE t2.[object_id] = f.parent_object_id
)
SET @rows += @@ROWCOUNT
END
SELECT OBJECT_NAME([object_id]) FROM @t

Now, a few words about soft dependencies. Most often, these script dependencies do not impede object deletion.

In the time of SQL Server 2000, all script dependencies could be found in sysdepends. In 2005, the system view sql_dependencies appeared. Starting from 2008, sql_expression_dependencies was added, which can contain more links than the preceding system views. But the most important innovation of the latter is the opportunity to display non-valid links.

This can be demonstrated by the example, in which an object was deleted, and the link to it exist

s:

[

sql]
CREATE TABLE dbo.Table1 (ID INT)
GO
CREATE PROCEDURE dbo.Proc1
AS BEGIN
SELECT * FROM Table1
END
GO
SELECT referenced_entity_name, OBJECT_NAME(referenced_id)
FROM sys.sql_expression_dependencies
WHERE referencing_id = OBJECT_ID('dbo.Proc1');
DROP TABLE dbo.Table1;
SELECT referenced_entity_name, OBJECT_NAME(referenced_id)
FROM sys.sql_expression_dependencies
WHERE referencing_id = OBJECT_ID('dbo.Proc1');
[/sql]

Using this feature, we can write a short query 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

Now let’s explore how to find dependencies for the table similarly to SSMS. I should say that Microsoft never seeks the easy way. For this reason, in SQL Server, you cannot get the hierarchy of dependencies by means of a simple query.

Here is the script to find places where the 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 1THEN o.[type]
WHEN 7THEN 'U'
WHEN 9THEN '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)
)

Like in SSMS, we can also find the table dependencies though foreign key

s:

[

sql]
INSERT INTO #h
SELECT parent_object_id, OBJECT_NAME(parent_object_id), SCHEMA_NAME([schema_id]), 'U'
FROM sys.foreign_keys
WHERE referenced_object_id = @obj_id
AND parent_object_id != referenced_object_id
[/sql]

And find out what synonyms use the tabl

e:

[

sql]
INSERT INTO #h
SELECT [object_id], name, SCHEMA_NAME([schema_id]), [type]
FROM sys.synonyms
WHERE OBJECT_ID(base_object_name) = @obj_id
[/sql]

The temporary table result

s:

[

sql]
SELECT DISTINCT *
FROM #h
WHERE ISNULL(obj_id, 0) != @obj_id
[/sql]

obj_id obj_name obj_schema obj_type
———– ———————————– ——————- ——–
7671075 vStoreWithContacts Sales V
39671189 vVendorWithContacts Purchasing V
103671417 ufnGetContactInformation dbo TF
247671930 uspGetEmployeeManagers dbo P
263671987 uspGetManagerEmployees dbo P
725577623 BusinessEntityContact Person U
997578592 Customer Sales U
1189579276 EmailAddress Person U
1237579447 Employee HumanResources U
1717581157 Password Person U
1755153298 iuPerson Person TR
1819153526 iduSalesOrderDetail Sales TR
1877581727 PersonCreditCard Sales U
1899153811 vAdditionalContactInfo Person V
1909581841 PersonPhone Person U
1915153868 vEmployee HumanResources V
1931153925 vEmployeeDepartment HumanResources V
1947153982 vEmployeeDepartmentHistory HumanResources V
1963154039 vIndividualCustomer Sales V
1979154096 vPersonDemographics Sales V
2091154495 vSalesPerson Sales V
2107154552 vSalesPersonSalesByFiscalYears Sales V

Even more challenging, when you need to know what the table depends from. In this case, the script will be hard to understand.

First of all, we need to analyze the scripted objects:

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 COLLATE database_default
, obj_schema SYSNAME NULL
, obj_type CHAR(5) NULL
, obj_db SYSNAME NULL
)
INSERT INTO #h (obj_id, obj_name, obj_schema, obj_type, obj_db)
SELECT
r.referenced_id
, COALESCE(o.name, t.name, r.referenced_entity_name)
, COALESCE(
SCHEMA_NAME(o.[schema_id])
, SCHEMA_NAME(t.[schema_id])
, r.referenced_schema_name
)
, CASE r.referenced_class
WHEN 1THEN o.[type]
WHEN 6THEN (CASE
WHEN t.is_assembly_type = 1 THEN 'CLR'
WHEN t.is_table_type = 1 THEN 'TT'
ELSE 'UT'
END)
WHEN 7THEN 'U'
WHEN 9THEN 'U'
WHEN 10 THEN 'XML'
WHEN 21 THEN 'PF'
END
, CASE WHEN DB_ID(r.referenced_database_name) IS NOT NULL
THEN DB_NAME(DB_ID(r.referenced_database_name))
ELSE ISNULL(r.referenced_database_name, DB_NAME())
END
FROM (
SELECT
d.referenced_id
, d.referenced_class
, d.referenced_entity_name
, d.referenced_schema_name
, d.referenced_database_name
FROM sys.sql_expression_dependencies d
WHERE d.referencing_id = @obj_id
AND d.referenced_server_name IS NULL
AND ISNULL(d.referenced_id, 0) != @obj_id
UNION
SELECT
d.referenced_id
, d.referenced_class
, d.referenced_entity_name
, d.referenced_schema_name
, d.referenced_database_name
FROM sys.objects o
JOIN sys.sql_expression_dependencies d ON d.referencing_id = o.OBJECT_ID
WHERE o.parent_object_id = @obj_id
AND d.referenced_server_name IS NULL
AND ISNULL(d.referenced_id, 0) != @obj_id
) r
LEFT JOIN sys.objects o ON r.referenced_class = 1
AND o.OBJECT_ID = r.referenced_id
AND DB_ID(ISNULL(r.referenced_database_name, DB_NAME())) = DB_ID()
LEFT JOIN sys.types t ON r.referenced_class = 6
AND t.user_type_id = r.referenced_id
WHERE r.referenced_database_name IS NULL
OR DB_ID(r.referenced_database_name) IS NOT NULL

After that, we should check what is used inside the child objects of the table. For example, a trigger can use a table from a different database:

DECLARE
@DB SYSNAME
, @SQL NVARCHAR(MAX)
DECLARE cur CURSOR FAST_FORWARD READ_ONLY LOCAL FOR
SELECT DISTINCT obj_db
FROM #h
WHERE obj_id IS NULL OR obj_type IS NULL OR obj_schema IS NULL
OPEN cur
FETCH NEXT FROM cur INTO @DB
WHILE @@FETCH_STATUS != -1 BEGIN
IF NOT EXISTS(
SELECT 1 FROM #h
WHERE obj_id IS NULL OR obj_type IS NULL OR obj_schema IS NULL
) BREAK
SET @SQL = '
USE [' + @DB + '];
UPDATE #h
SET
obj_id = o.object_id
, obj_name = o.name
, obj_schema = SCHEMA_NAME(o.schema_id)
, obj_type = o.type
FROM sys.objects o
WHERE o.object_id = OBJECT_ID(QUOTENAME(ISNULL(obj_schema, SCHEMA_NAME())) + ''.'' + QUOTENAME(obj_name))
AND #h.obj_db = DB_NAME()
AND #h.obj_id IS NULL'
IF @DB = DB_NAME() BEGIN
SET @SQL = @SQL + '
UPDATE #h
SET
obj_id = x.xml_collection_id
, obj_name = x.name
, obj_schema = SCHEMA_NAME(x.schema_id)
FROM sys.xml_schema_collections x
WHERE x.name = #h.obj_name COLLATE database_default
AND (#h.obj_schema IS NULL OR x.schema_id = SCHEMA_ID(#h.obj_schema))
AND #h.obj_type = ''XML''
AND #h.obj_db = DB_NAME()'
END
EXEC sys.sp_executesql @SQL
UPDATE #h
SET
obj_id = o.[object_id]
, obj_name = o.name
, obj_schema = s.name
, obj_type = o.TYPE
, obj_db = 'master'
FROM MASTER.sys.objects o
JOIN MASTER.sys.schemas s ON s.[schema_id] = o.[schema_id]
WHERE o.TYPE IN ('P', 'RF', 'PC')
AND o.[schema_id] = 1
AND o.name = #h.obj_name COLLATE database_default
AND (SCHEMA_ID(#h.obj_schema) = 1 OR #h.obj_schema IS NULL)
AND #h.obj_name LIKE 'sp/_%' ESCAPE '/'
AND #h.obj_id IS NULL
FETCH NEXT FROM cur INTO @DB
END
CLOSE cur
DEALLOCATE cur

And after that, we should look through hard dependencies, including:

1) Foreign ke

ys

[

sql]
INSERT INTO #h (obj_id, obj_name, obj_schema, obj_type, obj_db)
SELECT o.[object_id], o.name, SCHEMA_NAME(o.schema_id), 'U', DB_NAME()
FROM sys.foreign_keys fk
JOIN sys.objects o ON o.[object_id] = fk.referenced_object_id
WHERE fk.parent_object_id = @obj_id
AND fk.parent_object_id != fk.referenced_object_id
[/sql]

2) Rules and default objec

ts

[

sql]
INSERT INTO #h (obj_id, obj_name, obj_schema, obj_type, obj_db)
SELECT c.default_object_id, o.name, SCHEMA_NAME(o.[schema_id]), 'DO', DB_NAME()
FROM sys.columns c
JOIN sys.objects o ON o.[object_id] = c.default_object_id
WHERE c.[object_id] = @obj_id
AND c.default_object_id > 0
AND o.parent_object_id = 0
UNION ALL
SELECT c.rule_object_id, o.name, SCHEMA_NAME(o.schema_id), 'R', DB_NAME()
FROM sys.columns c
JOIN sys.objects o ON o.[object_id] = c.rule_object_id
WHERE c.[object_id] = @obj_id
AND c.rule_object_id > 0
[/sql]

3) XML schemas

INSERT INTO #h (obj_id, obj_name, obj_schema, obj_type, obj_db)
SELECT x.xml_collection_id, x.name, SCHEMA_NAME(x.[schema_id]), 'XML', DB_NAME()
FROM sys.column_xml_schema_collection_usages u
JOIN sys.xml_schema_collections x ON x.xml_collection_id = u.xml_collection_id
WHERE u.[object_id] = @obj_id

4) Partition schemas

INSERT INTO #h (obj_id, obj_name, obj_schema, obj_type, obj_db)
SELECT ps.data_space_id, ps.name, NULL, 'PS', DB_NAME()
FROM sys.indexes i
JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id
WHERE i.[object_id] = @obj_id

5) Full-text search objects

INSERT INTO #h (obj_id, obj_name, obj_schema, obj_type, obj_db)
SELECT c.fulltext_catalog_id, c.name, NULL, 'FTC', DB_NAME()
FROM sys.fulltext_index_catalog_usages i
JOIN sys.fulltext_catalogs c ON i.fulltext_catalog_id = c.fulltext_catalog_id
WHERE i.OBJECT_ID = @obj_id
UNION ALL
SELECT s.stoplist_id, s.name, NULL, 'FTS', DB_NAME()
FROM sys.fulltext_indexes i
JOIN sys.fulltext_stoplists s ON i.stoplist_id = s.stoplist_id
WHERE i.OBJECT_ID = @obj_id
UNION ALL
SELECT s.property_list_id, s.name, NULL, 'FP', DB_NAME()
FROM sys.fulltext_indexes i
JOIN sys.registered_search_property_lists s ON i.property_list_id = s.property_list_id
WHERE i.OBJECT_ID = @obj_id

And all this just to get a tiny list of objects:

obj_id obj_name obj_schema obj_type obj_db
———– —————————————- ————– ——– ——————–
629577281 BusinessEntity Person U AdventureWorks2012
260 Name dbo UT AdventureWorks2012
259 NameStyle dbo UT AdventureWorks2012
65536 AdditionalContactInfoSchemaCollection Person XML AdventureWorks2012
65537 IndividualSurveySchemaCollection Person XML AdventureWorks2012

For each type of object, SQL Server stores a lot of dependencies that are scattered in different system views. Hence, you cannot write one universal and at the same time simple query for all cases.

Therefore, in practice, all I can do is use a GUI… But which one? Personally, I do not like the SSMS approach in finding dependencies:

Not only that it forms its own hierarchy, it’s just too slow. Run the profiler and you will see a huge “universal” query SSMS makes, which is also long-running. The SSMS query uses cycles, in which large chunks of code are executed several times.

For this reason, when searching for dependencies I try to use third-party applications, among which my favorite one is dbForge Studio for SQL Server. You may ask why? It forms a query based on an object type. If we search dependencies for a view, the tool does not take into account dependencies that exist only for other types of objects (for example, for views, there is no need to look for dependencies by foreign keys). Due to such behavior, the query is generated dynamically and is executed faster than in

SSMS.

I

n dbForge Studio, dependencies are ordered hierarchically with indication of schema, which is not the case in SSMS:

At the same time, dbForge Studio for SQL Server has a great navigation option, which is absent in SSMS:

What I can say in conclusion?

Simply try dbForge Studio for SQL Server in action, and you will see that it greatly simplifies the search of dependencies. It shows dependencies that are not supported or incorrectly displayed in SSMS. And it’s done much faster than in the Microsoft’s product. For me, this is more than a convincing argument to use dbForge every day in development and analysis of database structures.

Reference: Pinal Dave (https://blog.sqlauthority.com)