title: C# parse SQL statement to find all INSERT/UPDATE/DELETE tables used in stored procedures
body: <p>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 -</p>
  
  <pre class="lang-cs prettyprint-override"><code>void Main()
  {
      string DBName = "Blah";
      string ServerName = @"(localdb)\MSSQLLocalDB";
  
      Server s = new Server(ServerName);
      Database db = s.Databases[DBName];
  
      ConcurrentDictionary&lt;string, SPAudit&gt; list = new ConcurrentDictionary&lt;string, SPAudit&gt;();
  
      var sps = db.StoredProcedures.Cast&lt;StoredProcedure&gt;()
      .Where(x =&gt; x.ImplementationType == ImplementationType.TransactSql  &amp;&amp; x.Schema == "dbo")
      .Select(x =&gt; new
      {
          x.Name,
          Body = x.TextBody
      }).ToList();
  
      Parallel.ForEach(sps, item =&gt;
      {
          try
          {
              ParseResult p = Parser.Parse(item.Body);
              IEnumerable&lt;SqlInsertStatement&gt; insStats = null;
              IEnumerable&lt;SqlUpdateStatement&gt; updStats = null;
              IEnumerable&lt;SqlDeleteStatement&gt; delStats = null;
              var listTask = new List&lt;Task&gt;();
              listTask.Add(Task.Run(() =&gt;
              {
                  insStats = FindBatchCollection&lt;SqlInsertStatement&gt;(p.Script.Batches);
              }));
              listTask.Add(Task.Run(() =&gt;
              {
                  updStats = FindBatchCollection&lt;SqlUpdateStatement&gt;(p.Script.Batches);
              }));
              listTask.Add(Task.Run(() =&gt;
              {
                  delStats = FindBatchCollection&lt;SqlDeleteStatement&gt;(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) =&gt; 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) =&gt; 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) =&gt; ll);
                      }
                  }
              }
          }
          catch (Exception ex)
          {
              Console.WriteLine(ex.Message);
          }
      });
  }
  
  IEnumerable&lt;T&gt; FindBatchCollection&lt;T&gt;(SqlBatchCollection coll) where T : SqlStatement
  {
      List&lt;T&gt; sts = new List&lt;T&gt;();
      foreach (var item in coll)
      {
          sts.AddRange(FindStatement&lt;T&gt;(item.Children));
      }
      return sts;
  }
  
  
  IEnumerable&lt;T&gt; FindStatement&lt;T&gt;(IEnumerable&lt;SqlCodeObject&gt; objs) where T : SqlStatement
  {
      List&lt;T&gt; sts = new List&lt;T&gt;();
      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&lt;T&gt;(item.Children));
              }
          }
      }
      return sts;
  }
  
  public class SPAudit
  {
      public HashSet&lt;string&gt; InsertTable { get; set; }
      public HashSet&lt;string&gt; UpdateTable { get; set; }
      public HashSet&lt;string&gt; DeleteTable { get; set; }
  
      public SPAudit()
      {
          InsertTable = new HashSet&lt;string&gt;();
          UpdateTable = new HashSet&lt;string&gt;();
          DeleteTable = new HashSet&lt;string&gt;();
      }
  }
  </code></pre>
  
  <p>Now I'm facing two problems</p>
  
  <ul>
  <li>First, its is taking hell lot of a time to complete, given that there are around 841 stored procedures in the database.</li>
  <li>Second, if there are statements like the following the table name is not being captured properly, meaning that the table is being captured as <code>w</code> instead of <code>SomeTable_1</code> or <code>SomeTable_2</code>.</li>
  </ul>
  
  <pre class="lang-sql prettyprint-override"><code>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
  </code></pre>
  
  <p>Any help would be greatly appreciated.</p>
  
  <h2>Edit</h2>
  
  <p>Using the following dll from this location <code>C:\Program Files (x86)\Microsoft SQL Server\140\DTS\Tasks</code>- </p>
  
  <ul>
  <li><code>Microsoft.SqlServer.ConnectionInfo.dll</code></li>
  <li><code>Microsoft.SqlServer.Management.SqlParser.dll</code></li>
  <li><code>Microsoft.SqlServer.Smo.dll</code></li>
  <li><code>Microsoft.SqlServer.SqlEnum.dll</code></li>
  </ul>