LINQed IN

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 HookedOnLINQ.com, a LINQ specific wiki reference site.
E-mail me Send mail

Disclaimer

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(
                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;
        }
    }
}

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

Comments

Comments are closed