SQL Server Object Dependency Viewer Revisited

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 alternative solution based on the new Microsoft Automatic Graph Layout features in Visual Studio 2010. The approach Doug takes builds a DGML XML file using PowerShell which then can be opened in Visual Studio 2010.

I thought it would be interesting to create an updated version of the SQL Server Object Dependency Viewer using DGML. Rather than simply running Doug’s script as-is I decided to develop an alternative solution targeted for SQL Server. Because the new solution only requires creating a DGML XML file and SQL Server can emit XML natively I used the following T-SQL/XQuery (no PowerShell required! Nonetheless I included a one-liner at the end of this post)

Requirements:

• SQL Server 2008 or higher database
• Visual Studio 2010

Run the following script from SQL Server Management Studio

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

;WITH
xmlnamespaces
(
DEFAULT 'http://schemas.microsoft.com/vs/2009/dgml'
)
,Links AS
(SELECT 1 AS 'DirectedGraph')
,Link AS
(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
AND o.type_desc != 'CHECK_CONSTRAINT')
 
SELECT
(
	SELECT [Source] AS "@Source", [Target] AS "@Target"
	FROM Link for xml path('Link'), type
)
FROM Links for xml AUTO, root('DirectedGraph'), type

Save the output as a dgml file, for example AdventureWorksLT.dgml. Next double-click to open the file in Visual Studio. You should see a dependency graph similar to this:

If you want to automate a the steps of saving and opening the DGML file. Save the T-SQL script above as dgml.sql and create a PowerShell script you can then call from sqlps host:

1
2
3

$fileName = "C:\Users\u00\Desktop\AdventureWorks.dgml"
Invoke-Sqlcmd -ServerInstance "Z003\R2" -Database "AdventureWorksLT" -InputFile "C:\Users\u00\Desktop\dgml.sql" -MaxCharLength 8000 | Select -ExpandProperty Column1 | Set-Content $fileName
invoke-item $fileName