Dependency Analysis of .NET and SQL Server Applications

Recently I starting making some small updates to a very cool dependency analysis application that I designed last year. The basic premise is to be able to trace dependencies from the database up through the user interface or vice versa in order to answer questions such as, if we change table XYZ, what stored procedures and ASP.NET forms will be affected?

The application is actually very good at answering those questions. Field level analysis was more difficult to achieve, so left that out, but at the object level (table/view/proc/class/form) it works pretty well.

I'll describe here, at a high level, the approach I took for each type of data. Let me know if you are interested in code samples for specific implementations!

To analyze .NET, I analyze the actual assemblies using disassembly and reflection. This was tricky but very do-able. Essentially I grab a copy of all the assemblies used by an application and load them "for reflection only" into a separate AppDomain. I gather some dependencies by looking at the types references in member signatures (using reflection) as well as referenced in code (using the disassembler). I used Lutz Roeder's .NET Reflector for the disassembler (http://blog.lutzroeder.com/), which RedGate now has available for free (http://reflector.red-gate.com/).

Also, on the .NET side, I parse configuration files for connection strings and I have "framework" specific code to look for particular attributes or other means that various projects I am analyzing have used to map .NET code to database objects.

To analyze SQL Server database schema, we used the RedGate tools and parsed the xml output. To analyze SSIS packages, we used the SQL Server 2005 Metadata Toolkit (which is actually on codeplex at http://code.msdn.microsoft.com/SqlServerMetadata/, contrary to what the download link at Microsoft http://www.microsoft.com/downloads/details.aspx?FamilyID=182bd330-0189-450c-a2fe-df5c132d9da9&displaylang=en says.).

To analyze stored procedure, functions, and views T-SQL code for dependencies, we used http://www.sqlparser.com/.

The basic idea is to record the objects I find and the relationships between those objects, and then provide ways to explore the data. I came up with a system to record two dimensions, or types, of relationships -- "contains" (e.g., a database contains a table) and "uses" (e.g. a stored procedure "uses" a table, or a .NET class "uses" a stored procedure, etc.).