SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get Dependent Databases, Tables, Columns…

Quite often people need to write a new T-SQL application to replace an existing one for various reasons. For instance, Existing report is generated by a complex T-SQL procedure which pulls data from OLTP directly. The performance of the procedre is poor since everything is calculated while report is running. Company decided to pre-calculate and save the result in a denormalized table, and the report runs off that denormalized table. Obviously, the original code need to be re-written. Let’s say you are new to the company. Before starting your work, you may want to know what databases, tables, and columns are referenced by the procedure. It will not be hard if the procedure code block only has few lines of code. However, if the procedure includes few or tens thousand lines, accomplishing such work might takes days. The worth thing is you might miss some refereces and finally you might abandon your work result at the end due to the inaccuracy.

How do you accurately find them out? There is no system functions supporting it. Fortunately, XML query plans include all information that we need. As long as we can get estimated query plans generated, we will be able to figure out all dependencies. For instance

set showplan_xml on
select * from sys.columns
set showplan_xml off

You will see the part of the estimated query plan of it

	<ColumnReference Database="[master]" Schema="[sys]" Table="[syscolpars]" Column="id" />
	<ColumnReference Database="[master]" Schema="[sys]" Table="[syscolpars]" Column="colid" />
	<ColumnReference Database="[master]" Schema="[sys]" Table="[syscolpars]" Column="name" />
	<ColumnReference Database="[master]" Schema="[sys]" Table="[syscolpars]" Column="xtype" />
	<ColumnReference Database="[master]" Schema="[sys]" Table="[syscolpars]" Column="utype" />
	<ColumnReference Database="[master]" Schema="[sys]" Table="[syscolpars]" Column="length" />
	<ColumnReference Database="[master]" Schema="[sys]" Table="[syscolpars]" Column="prec" />
	<ColumnReference Database="[master]" Schema="[sys]" Table="[syscolpars]" Column="scale" />
	<ColumnReference Database="[master]" Schema="[sys]" Table="[syscolpars]" Column="xmlns" />
	<ColumnReference Database="[master]" Schema="[sys]" Table="[syscolpars]" Column="dflt" />
	<ColumnReference Database="[master]" Schema="[sys]" Table="[syscolpars]" Column="chk" />
	<ColumnReference Column="Expr1003" />
	<ColumnReference Column="Expr1004" />
	<ColumnReference Column="Expr1005" />
	<ColumnReference Column="Expr1006" />
	<ColumnReference Column="Expr1007" />
	<ColumnReference Column="Expr1008" />
	<ColumnReference Column="Expr1009" />
	<ColumnReference Column="Expr1010" />
	<ColumnReference Column="Expr1011" />
	<ColumnReference Column="Expr1012" />
	<ColumnReference Column="Expr1013" />
	<ColumnReference Column="Expr1014" />
	<ColumnReference Column="Expr1015" />
	<ColumnReference Column="Expr1016" />

Now let’s implement CLR code to retrieve only the dependencies we need.

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Linq;
using System.Xml;
using System.Xml.Linq;
using Microsoft.SqlServer.Server;

public partial class SQLNotes
    public static Dictionary<string, string[]> GetDependency(XElement item, Dictionary<string, string[]> dep = null)
        if (dep == null)
            dep = new Dictionary<string, string[]>();
        string database = "", schema = "", table = "", column = "", key = "";
        foreach (XAttribute attr in item.Attributes())
            if (attr.Name == "Database") database = attr.Value;
            if (attr.Name == "Schema") schema = attr.Value;
            if (attr.Name == "Table") table = attr.Value;
            if (attr.Name == "Column") column = attr.Value;
        if ((database != "") && (schema != "") && (table != "") && (column != ""))
            key = database + "-" + schema + "-" + table + "-" + column;
            dep[key] = new string[4] { database, schema, table, column };
        foreach (XElement e in item.Elements())
            GetDependency(e, dep);
        return dep;

    [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read, SystemDataAccess = SystemDataAccessKind.Read, FillRowMethodName = "CLRGetDependencies_FillRow", TableDefinition = "DatabaseName nvarchar(128), SchemaName nvarchar(128), TableName nvarchar(128), ColumnName nvarchar(128)")]
    public static IEnumerable CLRGetDependencies(SqlString SQLStatement)
        using (SqlConnection ContextConnection = new SqlConnection("context connection = true"))
            SqlCommand cmd = ContextConnection.CreateCommand();
            cmd.CommandText = "select db_name()";
            cmd.CommandTimeout = 0;
            SqlDataReader r1 = cmd.ExecuteReader();
            SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder();
            sb.DataSource = ".";
            sb.Enlist = false;
            sb.InitialCatalog = r1[0].ToString();
            sb.IntegratedSecurity = true;
            using (SqlConnection connection = new SqlConnection(sb.ToString()))
                cmd.Connection = connection;
                cmd.CommandText = "SET SHOWPLAN_XML ON";
                cmd.CommandText = SQLStatement.Value;
                object o = cmd.ExecuteScalar();
                cmd.CommandText = "SET SHOWPLAN_XML OFF";
                return GetDependency(XElement.Parse(o.ToString()));
    public static void CLRGetDependencies_FillRow(object o, out SqlString DatabaseName, out SqlString SchemaName, out SqlString TableName, out SqlString ColumnName)

        string[] data = ((KeyValuePair<string, string[]>)o).Value;
        DatabaseName = new SqlString(data[0]);
        SchemaName = new SqlString(data[1]);
        TableName = new SqlString(data[2]);
        ColumnName = new SqlString(data[3]);

Let’s have a quick test

select * from dbo.CLRGetDependencies('select * from sys.columns')
DatabaseName  SchemaName  TableName       ColumnName
------------- ----------- --------------- ------------
[test]        [sys]       [syscolpars]    id
[test]        [sys]       [syscolpars]    colid
[test]        [sys]       [syscolpars]    name
[test]        [sys]       [syscolpars]    xtype
[test]        [sys]       [syscolpars]    utype
[test]        [sys]       [syscolpars]    length
[test]        [sys]       [syscolpars]    prec
[test]        [sys]       [syscolpars]    scale
[test]        [sys]       [syscolpars]    xmlns
[test]        [sys]       [syscolpars]    dflt
[test]        [sys]       [syscolpars]    chk
[test]        [sys]       [syscolpars]    collationid
[test]        [sys]       [syscolpars]    status
[test]        [sys]       [syscolpars]    number

(14 row(s) affected)

This is exactly what we want to see. It will be accurate ( if an estimated plan can be generated). In few cases, where the estimated plan could not be generated, this function will return error.


LinkedInEmailBlogger PostShare/Bookmark

John Huang’s Blog

John Huang, Microsoft Certified Master in SQL Server, SQL Server MVP, is an independent SQL Server consultant in Vancouver BC, Canada. He started using SQL Server for his projects in year 1994. He has architected and implemented many SQL and BI applications serving different industrial areas. He loves talking about SQL Server and discussing SQL Server technologies with others. He blogs at http://www.sqlnotes.info.


Leave a comment on the original post [www.sqlnotes.info, opens in a new window]

Loading comments...