NodeXL, PowerShell and building a visual SQL Dependency Graph

Chad Miller's Blog - Build Your Own SQL Server 2008 Object Dependency Viewer

“I saw a demonstration by  Doug Finke during the Windows PowerShell Virtual User Group Meeting #9 for displaying network graphs and thought this would be a great technique for visualizing SQL Server object dependencies. The Powershell code is available on Doug's blog post entitled PowerShell, Visualize the Peanut Butter Recall Data. The scripts he provides use the NodeXL .NET class libraries for creating network graphs.

…

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:

 

…”

Some of my favorite things, SQL Server, NodeXL, PowerShell and cool pictures… ;)

I so want to do something with NodeXL… Must see about doing something cool with it. I wonder if I could use it to visualize TFS work items (i.e. their relationships)? Or something at/ work, using the tons of SQL data we gather? Hum…