With the first piece of our do-it-yourself project in place, we need to get the dependency information in a simple source/target pair format to pass the data to the Show-NetMap function. Unfortunately obtaining reliable object dependencies in SQL Server is somewhat difficult due to deferred name resolution and other dependency tracking issues.
Aaron Bertrand has a very detailed post describing the problems with dependency tracking in SQL Server 6.5 through SQL Server 2008 entitled
Keeping sysdepends up to date in SQL Server 2008. As a result of the SQL dependency tracking issues most SQL Server professionals have learned not to trust the sysdepends tables. The only truly reliable method of determining dependencies remains to be parsing SQL code or purchasing 3rd party dependency viewer tools. I had originally planned on writing my own dependency parsing cmdlet, leveraging Visual Studio Database Edition ScriptDom class libraries, but quickly discovered the properties and methods which would expose this information is private. Fortunately SQL Server 2008 has added a new system catalog view called sys.sql_expression_dependencies which solves some, but not all of the dependency tracking issues (see Aaron’s post for details). The new system catalog view may still have some issues, but for the most part its good enough for getting SQL object dependencies without parsing SQL code, so this is what we will use.
Before we get started we’ll need to download
Doug’s functions and
SQL Server Powershell Extensions. The following code below uses the
SQL Server Powershell Extensions function, Get-SqlData to get the output of a query against sys.sql_expression_dependencies and sys.objects. In order to show a simplier graph I’m filtering out check constraint dependency information. The data is then piped to Doug’s Show-NetMap Powershell function:
. .\Show-NetMap
$qry = @”
SELECT DISTINCT
OBJECT_NAME(referencing_id) AS [Source],
COALESCE(referenced_server_name + ‘.’,”) + COALESCE(referenced_database_name + ‘.’,”)
+ COALESCE(referenced_schema_name + ‘.’,”) + referenced_entity_name AS [Target],
o.type_desc AS SourceType
FROM sys.sql_expression_dependencies AS sed
INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id
“@
get-sqldata ‘Z002\SQL2K8′ AdventureWorksLT $qry | ? {$_.SourceType -ne ‘CHECK_CONSTRAINT’} | Select Source, Target | Show-NetMap F
Network graph, showing a diagram of SQL Server object dependencies in the AdventureWorksLT sample database:
Alternative query that provides more detailed column level dependency information:
$qry = @”
SELECT
OBJECT_NAME(referencing_id) + COALESCE(‘.’ + COL_NAME(referencing_id, referencing_minor_id),”)
AS [Source],
COALESCE(referenced_server_name + ‘.’,”) + COALESCE(referenced_database_name + ‘.’,”)
+ COALESCE(referenced_schema_name + ‘.’,”) + referenced_entity_name
+ COALESCE(‘.’ + COL_NAME(referenced_id, referenced_minor_id), ”) AS [Target],
o.type_desc AS SourceType
FROM sys.sql_expression_dependencies AS sed
INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id
UNION ALL
SELECT OBJECT_NAME(referencing_id) AS [Source],
OBJECT_NAME(referencing_id) + ‘.’ + COL_NAME(referencing_id, referencing_minor_id) AS [Target],
o.type_desc AS SourceType
FROM sys.sql_expression_dependencies AS sed
INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id
WHERE referencing_minor_id <> 0
“@
[...] Doug Finke featured in his blog post PowerShell, Visualize the Peanut Butter Recall Data.. I’ve previously blogged about this script using it to create a complete database dependency map. To use Show-NetMap, source the script and [...]
[...] developer background to quickly fashion useful tools. For examples see my previous blog postings on Build your own SQL Dependency viewer and Disk, database and table space [...]
[...] year ago I blogged about building a SQL Server 2008 Object Dependency Viewer based on a script by PowerShell MVP, Doug Finke (Blog|Twitter). Since then Doug has created an [...]
[...] Server Object Dependency Viewer Revisited by Chad Miller A year ago I blogged about building a SQL Server 2008 Object Dependency Viewer based on a script by PowerShell MVP, Doug Finke (Blog|Twitter). Since then Doug has created an [...]