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 = 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.
