Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Chad Miller

Chad Miller is a Senior Manager of Database Administration at Raymond James Financial. Chad has worked with Microsoft SQL Server since 1999 and has been automating administration tasks using Windows Powershell since 2007. Chad is the Project Coordinator/Developer of the Powershell-based Codeplex project SQL Server PowerShell Extensions (SQLPSX). Chad leads the Tampa Powershell User Group and is a frequent speaker at users groups, SQL Saturdays and Code Camps.

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:

vsdepends1

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

Comments

Posted by meganruhl on 27 August 2010

I get the xml, but no graph?

Posted by cmille19 on 27 August 2010

Do you have Visual Studio 2010? Did you save the XML file as a dgml file?

Posted by viswavikass on 15 October 2012

DGML is not supported by VS 2010 Professional Edition. It is available only for Premium and Ultimate editions.

Leave a Comment

Please register or log in to leave a comment.