Blog by Troy Magennis on Software Architecture, Development and Management

About the author

Troy Magennis is a software developer living in Seattle, WA. Troy is a Microsoft MVP, the author of many articles, and the founder of, a LINQ specific wiki reference site.
E-mail me Send mail


The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

© Copyright 2014

Determining SQL Server Object Dependencies for a Stored Procedure or Other Database Object Name

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(
                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)


            // 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;

Categories: C# | LINQ | Resources
Posted by t_magennis on Tuesday, July 15, 2008 5:34 AM
Permalink | Comments (37) | Post RSSRSS comment feed


Comments are closed