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
go
select * from sys.columns
go
set showplan_xml off
go

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

 <OutputList>
	<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" />
</OutputList>

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"))
        {
            ContextConnection.Open();
            SqlCommand cmd = ContextConnection.CreateCommand();
            cmd.CommandText = "select db_name()";
            cmd.CommandTimeout = 0;
            SqlDataReader r1 = cmd.ExecuteReader();
            r1.Read();
            SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder();
            sb.DataSource = ".";
            sb.Enlist = false;
            sb.InitialCatalog = r1[0].ToString();
            r1.Close();
            sb.IntegratedSecurity = true;
            ContextConnection.Close();
            using (SqlConnection connection = new SqlConnection(sb.ToString()))
            {
                connection.Open();
                cmd.Connection = connection;
                cmd.CommandText = "SET SHOWPLAN_XML ON";
                cmd.ExecuteNonQuery();
                cmd.CommandText = SQLStatement.Value;
                object o = cmd.ExecuteScalar();
                cmd.CommandText = "SET SHOWPLAN_XML OFF";
                cmd.ExecuteNonQuery();
                connection.Close();
                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.

http://www.sqlnotes.info/

Get Dependent Databases, Tables, Columns…

You May Also Like

4 thoughts on “Get Dependent Databases, Tables, Columns…

  1. Could you please post an example for the SQL SERVER Function you need to create in order to call this CLR

    1. Tim, The CLR code is in the article as well. You can start a database project in Visual Studio, copy-past the code, after deploying, you will get the CLR function.

Leave a Reply to Bill Cancel reply

Your email address will not be published. Required fields are marked *

C# | HTML | Plain Text | SQL | XHTML | XML | XSLT |

This site uses Akismet to reduce spam. Learn how your comment data is processed.