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
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
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
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
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
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
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
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)
That is an awesome amount of sql goodness. I thought I knew the system views pretty well but your scripts to find dependent objects completely blows mine out of the water. They have been added to the toolbox. Thanks for all the hard work.
I feel a little nit picky given the amount of work that must have gone into this but in the script to find the child objects for Table1 there is reference to a variable @tables which I think should be @t.
Once again thanks for all the work that has gone into this and all of your other post which I follow avidly.
That`s really helpful !! Great work sir.
In your tsql to “search for non-existent objects inside functions, views, and stored procedures” you look at the referenced_class in the WHERE statement. But why is the referenced_ID NULL when in many cases it is a valid reference? I had 814 records where referenced_ID IS NULL, but only 95 rows were returned from your tsql. So why in many cases is the referenced_ID NULL?
BTW – super good article. I had been looking for something like this for a long while.
Am I right in thinking you cannot create a FK constraint using a synonym to an object in another schema in the same database, and do you know what that would be? You can create the constraint explicitly, so it just seems an omission.