C# parse SQL statement to find all INSERT/UPDATE/DELETE tables used in stored procedures

Question

As the title says my intention is to find all tables participating in either INSERT/UPDATE/DELETE statements and produce a structured format. So far this is what I've come up with -

void Main()
{
    string DBName = "Blah";
    string ServerName = @"(localdb)\MSSQLLocalDB";

    Server s = new Server(ServerName);
    Database db = s.Databases[DBName];

    ConcurrentDictionary<string, SPAudit> list = new ConcurrentDictionary<string, SPAudit>();

    var sps = db.StoredProcedures.Cast<StoredProcedure>()
    .Where(x => x.ImplementationType == ImplementationType.TransactSql  && x.Schema == "dbo")
    .Select(x => new
    {
        x.Name,
        Body = x.TextBody
    }).ToList();

    Parallel.ForEach(sps, item =>
    {
        try
        {
            ParseResult p = Parser.Parse(item.Body);
            IEnumerable<SqlInsertStatement> insStats = null;
            IEnumerable<SqlUpdateStatement> updStats = null;
            IEnumerable<SqlDeleteStatement> delStats = null;
            var listTask = new List<Task>();
            listTask.Add(Task.Run(() =>
            {
                insStats = FindBatchCollection<SqlInsertStatement>(p.Script.Batches);
            }));
            listTask.Add(Task.Run(() =>
            {
                updStats = FindBatchCollection<SqlUpdateStatement>(p.Script.Batches);
            }));
            listTask.Add(Task.Run(() =>
            {
                delStats = FindBatchCollection<SqlDeleteStatement>(p.Script.Batches);
            }));
            Task.WaitAll(listTask.ToArray());
            foreach (var ins in insStats)
            {
                var table = ins?.InsertSpecification?.Children?.FirstOrDefault();
                if (table != null)
                {
                    var tableName = table.Sql.Replace("dbo.", "").Replace("[", "").Replace("]", "");
                    if (!tableName.StartsWith("@"))
                    {
                        var ll = list.ContainsKey(item.Name) ? list[item.Name] : null;
                        if (ll == null)
                        {
                            ll = new SPAudit();
                        }
                        ll.InsertTable.Add(tableName);
                        list.AddOrUpdate(item.Name, ll, (key, old) => ll);
                    }
                }
            }
            foreach (var ins in updStats)
            {
                var table = ins?.UpdateSpecification?.Children?.FirstOrDefault();
                if (table != null)
                {
                    var tableName = table.Sql.Replace("dbo.", "").Replace("[", "").Replace("]", "");
                    if (!tableName.StartsWith("@"))
                    {
                        var ll = list.ContainsKey(item.Name) ? list[item.Name] : null;
                        if (ll == null)
                        {
                            ll = new SPAudit();
                        }
                        ll.UpdateTable.Add(tableName);
                        list.AddOrUpdate(item.Name, ll, (key, old) => ll);
                    }
                }
            }
            foreach (var ins in delStats)
            {
                var table = ins?.DeleteSpecification?.Children?.FirstOrDefault();
                if (table != null)
                {
                    var tableName = table.Sql.Replace("dbo.", "").Replace("[", "").Replace("]", "");
                    if (!tableName.StartsWith("@"))
                    {
                        var ll = list.ContainsKey(item.Name) ? list[item.Name] : null;
                        if (ll == null)
                        {
                            ll = new SPAudit();
                        }
                        ll.DeleteTable.Add(tableName);
                        list.AddOrUpdate(item.Name, ll, (key, old) => ll);
                    }
                }
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    });
}

IEnumerable<T> FindBatchCollection<T>(SqlBatchCollection coll) where T : SqlStatement
{
    List<T> sts = new List<T>();
    foreach (var item in coll)
    {
        sts.AddRange(FindStatement<T>(item.Children));
    }
    return sts;
}


IEnumerable<T> FindStatement<T>(IEnumerable<SqlCodeObject> objs) where T : SqlStatement
{
    List<T> sts = new List<T>();
    foreach (var item in objs)
    {
        if (item.GetType() == typeof(T))
        {
            sts.Add(item as T);
        }
        else
        {
            foreach (var sub in item.Children)
            {
                sts.AddRange(FindStatement<T>(item.Children));
            }
        }
    }
    return sts;
}

public class SPAudit
{
    public HashSet<string> InsertTable { get; set; }
    public HashSet<string> UpdateTable { get; set; }
    public HashSet<string> DeleteTable { get; set; }

    public SPAudit()
    {
        InsertTable = new HashSet<string>();
        UpdateTable = new HashSet<string>();
        DeleteTable = new HashSet<string>();
    }
}

Now I'm facing two problems

• First, its is taking hell lot of a time to complete, given that there are around 841 stored procedures in the database.
• Second, if there are statements like the following the table name is not being captured properly, meaning that the table is being captured as w instead of SomeTable_1 or SomeTable_2.

CREATE PROCEDURE [dbo].[sp_blah]
    @t SomeTableType READONLY
AS  

    DELETE w
    FROM SomeTable_2 w
    INNER JOIN (Select * from @t) t
    ON w.SomeID = t.SomeID

    DELETE w
    FROM SomeTable_1 w
    INNER JOIN (Select * from @t) t
    ON w.SomeID = t.SomeID


RETURN 0

Any help would be greatly appreciated.

Edit

Using the following dll from this location C:\Program Files (x86)\Microsoft SQL Server\140\DTS\Tasks-

• Microsoft.SqlServer.ConnectionInfo.dll
• Microsoft.SqlServer.Management.SqlParser.dll
• Microsoft.SqlServer.Smo.dll
• Microsoft.SqlServer.SqlEnum.dll

Answer 1

Finally I got it to work like I wanted the output to look like using @dlatikay answer. I'm posting this here more for documentation purposes than anything else.

I'm using the following nuget packages -

• https://www.nuget.org/packages/Microsoft.SqlServer.SqlManagementObjects/
• https://www.nuget.org/packages/Microsoft.SqlServer.TransactSql.ScriptDom/

and removed all other local dependencies. I hope this helps someone out there.

void Main()
{
    string DatabaseName = "Blah";
    string ServerIP = @"(localdb)\MSSQLLocalDB";
    List<string> ExcludeList = new List<string>()
    {
        "sp_upgraddiagrams",
        "sp_helpdiagrams",
        "sp_helpdiagramdefinition",
        "sp_creatediagram",
        "sp_renamediagram",
        "sp_alterdiagram",
        "sp_dropdiagram"
    };

    List<string> StringDataTypes = new List<string>()
    {
        "nvarchar",
        "varchar",
        "nchar",
        "char",
    };

    Server s = new Server(ServerIP);
    s.SetDefaultInitFields(typeof(StoredProcedure), "IsSystemObject");
    Database db = s.Databases[DatabaseName];

    Dictionary<string, SPAudit> AuditList = new Dictionary<string, SPAudit>();

    var sps = db.StoredProcedures.Cast<StoredProcedure>()
    .Where(x => x.ImplementationType == ImplementationType.TransactSql && x.Schema == "dbo" && !x.IsSystemObject)
    .Select(x => new
    {
        x.Name,
        Body = x.TextBody,
        Parameters = x.Parameters.Cast<StoredProcedureParameter>().Select(t =>
        new SPParam()
        {
            Name = t.Name,
            DefaultValue = t.DefaultValue,
            DataType = $"{t.DataType.Name}{(StringDataTypes.Contains(t.DataType.Name) ? $"({(t.DataType.MaximumLength > 0 ? Convert.ToString(t.DataType.MaximumLength) : "MAX")})" : "")}"
        })
    }).ToList();

    foreach (var item in sps)
    {
        try
        {
            TSqlParser parser = new TSql140Parser(true, SqlEngineType.Standalone);
            IList<ParseError> parseErrors;
            TSqlFragment sqlFragment = parser.Parse(new StringReader(item.Body), out parseErrors);
            sqlFragment.Accept(new OwnVisitor(ref AuditList, item.Name, item.Parameters));
        }
        catch (Exception ex)
        {
            //Handle exception
        }
    }
}

public class OwnVisitor : TSqlFragmentVisitor
{
    private string spname;
    private IEnumerable<SPParam> parameters;
    private Dictionary<string, SPAudit> list;

    public OwnVisitor(ref Dictionary<string, SPAudit> _list, string _name, IEnumerable<SPParam> _parameters)
    {
        list = _list;
        spname = _name;
        parameters = _parameters;
    }

    public override void ExplicitVisit(InsertStatement node)
    {
        NamedTableReference namedTableReference = node?.InsertSpecification?.Target as NamedTableReference;
        if (namedTableReference != null)
        {
            string table = namedTableReference?.SchemaObject.BaseIdentifier?.Value;
            if (!string.IsNullOrWhiteSpace(table) && !table.StartsWith("#"))
            {
                if (!list.ContainsKey(spname))
                {
                    SPAudit ll = new SPAudit();
                    ll.InsertTable.Add(table);
                    ll.Parameters.AddRange(parameters);
                    list.Add(spname, ll);
                }
                else
                {
                    SPAudit ll = list[spname];
                    ll.InsertTable.Add(table);
                }
            }
        }
        base.ExplicitVisit(node);
    }

    public override void ExplicitVisit(UpdateStatement node)
    {
        NamedTableReference namedTableReference;
        if (node?.UpdateSpecification?.FromClause != null)
        {
            namedTableReference = node?.UpdateSpecification?.FromClause?.TableReferences[0] as NamedTableReference;
        }
        else
        {
            namedTableReference = node?.UpdateSpecification?.Target as NamedTableReference;
        }
        string table = namedTableReference?.SchemaObject.BaseIdentifier?.Value;
        if (!string.IsNullOrWhiteSpace(table) && !table.StartsWith("#"))
        {
            if (!list.ContainsKey(spname))
            {
                SPAudit ll = new SPAudit();
                ll.UpdateTable.Add(table);
                ll.Parameters.AddRange(parameters);
                list.Add(spname, ll);
            }
            else
            {
                SPAudit ll = list[spname];
                ll.UpdateTable.Add(table);
            }
        }
        base.ExplicitVisit(node);
    }

    public override void ExplicitVisit(DeleteStatement node)
    {
        NamedTableReference namedTableReference;
        if (node?.DeleteSpecification?.FromClause != null)
        {
            namedTableReference = node?.DeleteSpecification?.FromClause?.TableReferences[0] as NamedTableReference;
        }
        else
        {
            namedTableReference = node?.DeleteSpecification?.Target as NamedTableReference;
        }
        if (namedTableReference != null)
        {
            string table = namedTableReference?.SchemaObject.BaseIdentifier?.Value;
            if (!string.IsNullOrWhiteSpace(table) && !table.StartsWith("#"))
            {
                if (!list.ContainsKey(spname))
                {
                    SPAudit ll = new SPAudit();
                    ll.DeleteTable.Add(table);
                    ll.Parameters.AddRange(parameters);
                    list.Add(spname, ll);
                }
                else
                {
                    SPAudit ll = list[spname];
                    ll.DeleteTable.Add(table);
                }
            }
        }
        base.ExplicitVisit(node);
    }
}

public class SPAudit
{
    public HashSet<string> InsertTable { get; set; }
    public HashSet<string> UpdateTable { get; set; }
    public HashSet<string> DeleteTable { get; set; }
    public List<SPParam> Parameters { get; set; }

    public SPAudit()
    {
        InsertTable = new HashSet<string>();
        UpdateTable = new HashSet<string>();
        DeleteTable = new HashSet<string>();
        Parameters = new List<SPParam>();
    }
}

public class SPParam
{
    public string Name { get; set; }
    public string DefaultValue { get; set; }
    public string DataType { get; set; }
}