Finding what dependencies a Stored Procedure has on underlying tables, views, functions, etc is often necessary when trying to assess the impact of a change. SQL Server has built-in functions that will indicate in most cases a dependency for any object in the database. The system view "sys.sql_dependencies" is viewed with skepticism by some people who have obviously been bitten in the past.
In order to see for myself the results, I wrote a simple helper class, and thought i'd share the boilerplate code to start you off here (I may clean it up and share it as a library, email me if you have difficulty getting it running). Its a rough prototype, but it is returning good results for my purposes.
Note: This code requires Visual Studio 2008. It uses LINQ to SQL in a very loose way due to the LINQ to SQL Designer not listing the System Views and Functions. Its a good example of just how flexible LINQ to SQL is though.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;
using System.Configuration;
namespace DatabaseDependencyCrawler
{
public class SysDependsResult
{
public int referenced_major_id { get; set; }
}
public class ObjectInfoResult
{
public int id { get; set; }
public string name { get; set; }
public string xtype { get; set; }
public DateTime crdate { get; set; }
}
public class DatabaseDependencyCrawler
{
public List<DatabaseDependencyEntity> GetDBObjectDependencies(string connectionString, string name)
{
// the system views built-into SQL Server 2005
string dependsQuery = "select referenced_major_id from sys.sql_dependencies where object_id = object_id('{0}')";
string objectInfoQuery = "select * from sys.sysobjects where id in ( {0} )";
List<DatabaseDependencyEntity> result = new List<DatabaseDependencyEntity>();
// find the list of dependencies based upon a database object's name
DataContext context = new DataContext(connectionString);
var dependencies = (IEnumerable<dependencies>)context.ExecuteQuery(
typeof(SysDependsResult),
string.Format(dependsQuery, name),
new object[] { });
// build a list of object_is's to we can ask for their name in a second query
StringBuilder ids = new StringBuilder();
foreach (SysDependsResult d in dependencies)
{
if (ids.Length > 0)
ids.Append(",");
ids.Append(d.referenced_major_id);
}
// if any records were found...lookup the names of those id's comma separated
if (ids.Length > 0)
{
IEnumerable<ObjectInfoResult> objects = (IEnumerable<ObjectInfoResult>)context.ExecuteQuery(typeof(ObjectInfoResult),
string.Format(objectInfoQuery, ids.ToString()),
new object[] { });
foreach (ObjectInfoResult o in objects)
{
result.Add (
new DatabaseDependencyEntity {
DatabaseConnectionString = connectionString,
SourceObject = name,
Dependent = o
});
}
}
return result;
}
}
}