title: Parsing T-SQL To Extract Part of WHERE Clause
body: <p>I have a large SQL database containing 'curves'.  Each curve has an ID (curveid).  I'm trying to determine main users of each curve and if it is used at all.  To enable this, the DBAs are providing logs of all statements executed against the database.</p>
  
  <p>These statements can very in complexity.  All I want to do is extract what curveids are being queried for.</p>
  
  <p>Example statement follows:</p>
  
  <pre><code>WITH G AS ( SELECT [Timevalue] FROM [mc].[GranularityLookup] 
  WHERE [TimeValue] BETWEEN '19-Jul-2017 00:00' AND '30-Sep-2017 00:00' 
  AND [1 Hr] = 1), 
  D AS ( SELECT [CurveID], [DeliveryDate], [PublishDate], AVG([Value]) Value, MAX([PeriodNumber]) PeriodNumber 
  FROM mc.CURVEID_6657_1_LATEST data 
  JOIN 
  (SELECT CurveID ID, DeliveryDate dDate, MAX(PublishDate) pDate 
  FROM mc.CURVEID_6657_1_LATEST
  WHERE CurveID = 90564
      AND DeliveryDate &gt;= '19-Jul-2017 00:00' AND DeliveryDate &lt;= '30-Sep-2017 00:00'
  GROUP BY DeliveryDate,  CurveID ) Dates 
  ON data.DeliveryDate = dates.dDate AND data.PublishDate = dates.pDate 
  WHERE data.CurveID = 90564
  AND data.DeliveryDate &gt;= '19-Jul-2017 00:00' AND data.DeliveryDate &lt;= '30-Sep-2017 00:00'
  GROUP BY [CurveID], [PublishDate], [DeliveryDate] )
  SELECT 
  G.[TimeValue] [Deliver
  yDate] , D.[PublishDate], D.[Value], D.[PeriodNumber]
  FROM 
  G
  LEFT JOIN 
  D
  ON 
  G.[TimeValue] = D.[DeliveryDate]
  ORDER BY DeliveryDate ASC, PeriodNumber ASC, publishDate DESC
  </code></pre>
  
  <p>From this statement, all I'm interested in is extracting that the user queried for curveid 90564.</p>
  
  <p>The statement may also resemble either of the following:</p>
  
  <pre><code>SELECT * FROM anytable WHERE curveid = 123 AND deliverydate BETWEEN '2017-01-01' AND 2017-02-01'
  </code></pre>
  
  <p>or</p>
  
  <pre><code>SELECT * FROM mc.anytable WHERE curveid IN (1,2,3,4,5,6,7)
  </code></pre>
  
  <p>Again, all I want to know is the curve IDs.  I don't care about any of the other clauses.</p>
  
  <p>I'm using the Microsoft.SqlServer.TransactSql.ScriptDom namespace to parse the SQL and have got to the point where I can identify all WHERE statements using code similar to below (pieced together from some other samples):</p>
  
  <pre><code>string sql = @"WITH 
              G AS ( SELECT [Timevalue] FROM [mc].[GranularityLookup] 
              WHERE [TimeValue] BETWEEN '19-Jul-2017 00:00' AND '30-Sep-2017 00:00' 
              AND [1 Hr] = 1), 
              D AS ( SELECT [CurveID], [DeliveryDate], [PublishDate], AVG([Value]) Value, MAX([PeriodNumber]) PeriodNumber 
              FROM mc.CURVEID_6657_1_LATEST data 
              JOIN 
              (SELECT CurveID ID, DeliveryDate dDate, MAX(PublishDate) pDate 
              FROM mc.CURVEID_6657_1_LATEST
              WHERE CurveID = 90564
                  AND DeliveryDate &gt;= '19-Jul-2017 00:00' AND DeliveryDate &lt;= '30-Sep-2017 00:00'
              GROUP BY DeliveryDate,  CurveID ) Dates 
              ON data.DeliveryDate = dates.dDate AND data.PublishDate = dates.pDate 
              WHERE data.CurveID = 90564
              AND data.DeliveryDate &gt;= '19-Jul-2017 00:00' AND data.DeliveryDate &lt;= '30-Sep-2017 00:00'
              GROUP BY [CurveID], [PublishDate], [DeliveryDate] )
              SELECT 
              G.[TimeValue] [Deliver
              yDate] , D.[PublishDate], D.[Value], D.[PeriodNumber]
              FROM 
              G
              LEFT JOIN 
              D
              ON 
              G.[TimeValue] = D.[DeliveryDate]
              ORDER BY DeliveryDate ASC, PeriodNumber ASC, publishDate DESC";
              var parser = new TSql120Parser(false);
  
              IList&lt;ParseError&gt; errors;
              var fragment = parser.Parse(new StringReader(sql), out errors);
  
              var whereVisitor = new WhereVisitor();
              fragment.Accept(whereVisitor);
  
              //  I now have all WHERE clauses in whereVisitor.WhereStatements
  
  class WhereVisitor : TSqlConcreteFragmentVisitor
  {
      public readonly List&lt;WhereClause&gt; WhereStatements = new List&lt;WhereClause&gt;();
  
      public override void Visit(WhereClause node)
      {
          WhereStatements.Add(node);
      }
  
  }
  </code></pre>
  
  <p>Each of the clauses in whereVisitor.WhereStatements (3 in this example) expose a property called SearchCondition.  Unfortunately, this is where I run out of ideas.  What I want to achieve is logic as per below:</p>
  
  <pre><code>foreach (var clause in whereVisitor.WhereStatements)
  {
      //  IF any part of the clause filters based on curveid THEN
  
      //        Capture curveIDs
  
      //  END IF
  }
  </code></pre>
  
  <p>Other details:</p>
  
  <ul>
  <li>Using C# (.net 4.0)</li>
  <li>SQL Server 2008</li>
  <li>DLL for this is Microsoft.SqlServer.TransactSql.ScriptDom (located in my case at 'c:\Program Files (x86)\Microsoft SQL Server\130\Tools\PowerShell\Modules\SQLPS\Microsoft.SqlServer.TransactSql.ScriptDom.dll')</li>
  </ul>
  
  <p><strong>Edit 1</strong></p>
  
  <p>Some additional info:</p>
  
  <ul>
  <li>CurveID is a key to another table.  It would not make sense in this
  case to operate on it (e.g. curveId+1 or curveId &lt;= 10).</li>
  </ul>
  
  <p><strong>Edit 2 (Partial Solution)</strong></p>
  
  <p>Having the following visitor helps with the case where clause resembles
  curveid = 123:</p>
  
  <pre><code>class CurveIdVisitor : TSqlConcreteFragmentVisitor
  {
      public readonly List&lt;int&gt; CurveIds = new List&lt;int&gt;();
  
      public override void Visit(BooleanComparisonExpression exp)
      {
          if (exp.FirstExpression is ColumnReferenceExpression &amp;&amp; exp.SecondExpression is IntegerLiteral )
          {
              //  there is a possibility that this is of the ilk 'curveid = 123'
              //  we will look for the 'identifier'
              //  we take the last if there are multiple.  Example:
              //      alias.curveid
              //  goives two identifiers: alias and curveid
              if (
                  ((ColumnReferenceExpression) exp.FirstExpression).MultiPartIdentifier.Identifiers.Last().Value.ToLower() ==
                  "curveid")
              {
                  //  this is definitely a curveid filter
                  //  Now to find the curve id
                  int curveid = int.Parse(((IntegerLiteral) exp.SecondExpression).Value);
                  CurveIds.Add(curveid);
              }
          }
  </code></pre>