{"id":2089,"date":"2015-09-24T00:00:00","date_gmt":"2015-09-24T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/dependencies-and-references-in-sql-server\/"},"modified":"2026-03-09T13:44:49","modified_gmt":"2026-03-09T13:44:49","slug":"dependencies-and-references-in-sql-server","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/dependencies-and-references-in-sql-server\/","title":{"rendered":"SQL Server Dependencies: Find & Track Object References"},"content":{"rendered":"\n
\n

SQL Server objects – tables, views, procedures, functions, triggers, types, and constraints – form a network of dependencies. Before you can safely rename, alter, or drop any object, you need to know what depends on it and what it depends on. SQL Server provides three approaches: the SSMS Object Explorer dependency viewer (visual, convenient), dynamic management views (sys.sql_expression_dependencies, sys.dm_sql_referencing_entities, sys.dm_sql_referenced_entities) for scripted queries, and PowerShell via SMO for automation. Dependencies are classified as soft (schema-bound – SQL Server prevents breaking changes) or hard (unvalidated references that may break silently). This article demonstrates all three approaches with AdventureWorks examples.<\/p>\n

Introduction<\/h2>\n

In a relational database, it isn’t just the data that is related, but the database objects themselves. A view, for example, that references tables is dependent upon them, and wherever that view is used the function, procedure or view that uses it depends on it. Those tables referred to by the view may in turn contain user-defined types, or could be referenced to other tables via constraints. By its very nature, any SQL Server database will contain a network of inter-dependencies.<\/p>\n

SQL Server objects, such as tables, routines and views, often depend on other objects, either \u00a0because they refer to them in SQL Expressions, have constraints that access them, or use them. \u00a0There may be other objects that are, in turn, dependent on them. Dependencies grow like nets. It isn’t just foreign keys or SQL references that cause dependencies, but a whole range of objects such as triggers, user-defined types and rules. \u00a0It can complicate any changes to a database by requiring a specific order of operations within a database build script, or migration script. If you get it wrong, you’ll get a whole range of errors like “Cannot drop xxx ‘MyName’ because it is being referenced by object ‘HisName’ There may be other objects that reference this yyy”. or ” xxx ‘MyName’ references invalid xxx ‘HerName’.” Basically, objects need to be deleted or altered in a particular order. In a well-designed SQL Server database, or set of linked databases, it is easy to determine these dependencies, and work out the right sequence for doing things.<\/p>\n

Finding dependencies via SSMS<\/h2>\n

Most of us need to think very little about finding out about dependencies, since SMO allows SSMS to get dependency information for us for any list of database objects, and display it in a tree structure. If you wish to know what dependencies an object has, or what it in turn depends on, You just right-click the object in the object explorer pane and click on ‘view dependencies’ in the context menu that then appears.\u00a0<\/p>\n

\"2284-clip_image001\"<\/figure>\n

\u00a0<\/p>\n

Your re-engineering work must take these dependencies into account. If you need to, for example, delete a column, your work must start at the ‘leaves’ to make sure that nothing untoward references that column. If you change a user-defined table type, then you need to check wherever it has been used in the database. SSMS uses SMO to get this information. You can get the same information yourself, if you need to, by using a PowerShell script to get the same information from SMO. Accessing, in this example, the same table from the same database …<\/p>\n

Import-Module sqlserver -DisableNameChecking\n#load the SQLPS functionality for getting the registered servers\n\n#-- just to save typing ----\n$MS = 'Microsoft.SQLServer'\n$My = \"$MS.Management.Smo\"\n$Mc = \"$MS.Management.Common\"\n\n$dbname = \"Adventureworks2016\" #the database we want\n$tableName = 'Employee' #the table to investigate\n$schemaname = 'HumanResources' #the schema that the table is in\n$serverName = \"Philf01\" #the name of the server\n$credentials = 'user=Philfactor;pwd=ismellofpoo4U'\n# if SQL Server credentials, use 'user id=\"MyID;password=MyPassword\"'\n$DoWeLookForParentObjects = $false\n#look for the parent objects for each object-set to false for child objects\n\n#create the connection string\n$connectionString = `\n\"Data Source=$serverName;$credentials;pooling=False;multipleactiveresultsets=False;\"\n#connect to the server\ntry\n{\n  $sqlConnection = `\n    new-object System.Data.SqlClient.SqlConnection $connectionString\n  $conn = new-object \"$Mc.ServerConnection\" $sqlConnection\n  $srv = new-object Microsoft.SqlServer.Management.Smo.Server $conn\n}\ncatch\n{\n  \"Could not connect to SQL Server instance '$_.servername': $($error[0].ToString() +\n    $error[0].InvocationInfo.PositionMessage). Script is aborted\"\n  exit -1\n}\n#now get the SMO object for the table that we want.\n$success = $false\nif ($srv.Databases[$dbname] -ne $null)\n{\n  $db = $srv.Databases[$dbname]\n  if ($db.Tables.Contains($tableName, $schemaName))\n  { $table = new-object \"$My.Table\" ($db, $tableName, $schemaName);\n     $success = $true;}\n}\nif (-not $success)\n{\n  Write-error \"Couldn't find the $TableName table in the $dbname database\"\n  exit -1\n}\n#now  we set up the scripter object\n$scr = New-Object \"$My.Scripter\"\n#now choose options for the scripter that we need to get dependency order\n$options = New-Object \"$My.ScriptingOptions\"\n$options.DriAll = $True\n$options.AllowSystemObjects = $false\n$options.WithDependencies = $True\n$scr.Options = $options\n$scr.Server = $srv\n\n$VerbosePreference = \"Continue\"\n#we set up a URNcollection to contain our objects for analysis\n# (only one in this example)\n$urnCollection = new-object \"$my.UrnCollection\"\n$urnCollection.Add([Microsoft.SqlServer.Management.Sdk.Sfc.Urn]$table.Urn)\n#now we set up an event listnenr go get progress reports\n$ProgressReportEventHandler = [Microsoft.SqlServer.Management.Smo.ProgressReportEventHandler]  `\n   { Write-Verbose \"analysed '$($_.Current.GetAttribute('Name'))'\" }\n$scr.add_DiscoveryProgress($ProgressReportEventHandler)\n#create the dependency tree\n$dependencyTree =  `\n   $scr.DiscoverDependencies($urnCollection, $DoWeLookForParentObjects) \n#look for the parent objects for each object\n#and walk the dependencies to get the dependency tree.\n$depCollection = $scr.WalkDependencies($dependencyTree);\n#now we just show the dependency tree!\n$depCollection | %{\n  \"$(if ($_.IsRootNode -eq $true) { 'root: ' }\n     ) $($_.Urn.GetAttribute('Schema', $_.Urn.Type)\n     ).$($_.Urn.GetAttribute('Name', $_.Urn.Type)\n     )--$($_.Urn.Type)\"\n}\n\n$VerbosePreference = \"SilentlyContinue\"<\/pre>\n
Is there another way of doing this, hopefully in SQL? Well, yes there is, but I’ll be showing how to do that at the end of the article, and  providing the code<\/a>. But firstly, I’ll need to explain where some of the complications are.<\/p>\n
Soft and hard Dependencies<\/h2>\n
Dependencies are of two types. \u00a0There are ‘soft’ dependencies; references to other objects in SQL code that are exposed by sys.sql_expression_dependencies, and ‘hard’ dependencies that are exposed by the object catalog views. ‘Hard’ dependencies are inherent in the structure of the database, whereas code can reference objects in another database on the same server or on another server.<\/p>\n
Soft Dependencies<\/h3>\n
\n
\u00a0These soft dependencies are all recorded and are available from the sys.sql_expression_dependencies<\/code>. If someone has allowed ad-hoc SQL to be generated by applications rather than use stored procedures or functions, you are free to weep at this point, because you have missed out on getting all this essential information and will find it very hard to refactor your database.<\/p>\n
‘Soft’ Dependencies happen when you have a routine (that is a procedure, function rule, constraint or anything else with code in it) that refers to another entity, possibly in another database. By dint of a SQL Expression in, say, a View, you can make that view dependent on one or more other objects. This dependency information is maintained by the database, but not for rules, defaults, temporary tables, temporary stored procedures, or system objects, and only when the referenced entity appears by name in a persisted SQL expression of the referencing entity.<\/p>\n
There are two types of soft dependency<\/p>\n