c# - Parsing T-SQL To Extract Part of WHERE Clause -

i have large sql database containing 'curves'. each curve has id (curveid). i'm trying determine main users of each curve , if used @ all. enable this, dbas providing logs of statements executed against database.

these statements can in complexity. want extract curveids being queried for.

example statement follows:

with g ( select [timevalue] [mc].[granularitylookup]  [timevalue] between '19-jul-2017 00:00' , '30-sep-2017 00:00'  , [1 hr] = 1),  d ( select [curveid], [deliverydate], [publishdate], avg([value]) value, max([periodnumber]) periodnumber  mc.curveid_6657_1_latest data  join  (select curveid id, deliverydate ddate, max(publishdate) pdate  mc.curveid_6657_1_latest curveid = 90564     , deliverydate >= '19-jul-2017 00:00' , deliverydate <= '30-sep-2017 00:00' group deliverydate,  curveid ) dates  on data.deliverydate = dates.ddate , data.publishdate = dates.pdate  data.curveid = 90564 , data.deliverydate >= '19-jul-2017 00:00' , data.deliverydate <= '30-sep-2017 00:00' group [curveid], [publishdate], [deliverydate] ) select  g.[timevalue] [deliver ydate] , d.[publishdate], d.[value], d.[periodnumber]  g left join  d on  g.[timevalue] = d.[deliverydate] order deliverydate asc, periodnumber asc, publishdate desc 

from statement, i'm interested in extracting user queried curveid 90564.

the statement may resemble either of following:

select * anytable curveid = 123 , deliverydate between '2017-01-01' , 2017-02-01' 

or

select * mc.anytable curveid in (1,2,3,4,5,6,7) 

again, want know curve ids. don't care of other clauses.

i'm using microsoft.sqlserver.transactsql.scriptdom namespace parse sql , have got point can identify statements using code similar below (pieced other samples):

string sql = @"with              g ( select [timevalue] [mc].[granularitylookup]              [timevalue] between '19-jul-2017 00:00' , '30-sep-2017 00:00'              , [1 hr] = 1),              d ( select [curveid], [deliverydate], [publishdate], avg([value]) value, max([periodnumber]) periodnumber              mc.curveid_6657_1_latest data              join              (select curveid id, deliverydate ddate, max(publishdate) pdate              mc.curveid_6657_1_latest             curveid = 90564                 , deliverydate >= '19-jul-2017 00:00' , deliverydate <= '30-sep-2017 00:00'             group deliverydate,  curveid ) dates              on data.deliverydate = dates.ddate , data.publishdate = dates.pdate              data.curveid = 90564             , data.deliverydate >= '19-jul-2017 00:00' , data.deliverydate <= '30-sep-2017 00:00'             group [curveid], [publishdate], [deliverydate] )             select              g.[timevalue] [deliver             ydate] , d.[publishdate], d.[value], d.[periodnumber]                          g             left join              d             on              g.[timevalue] = d.[deliverydate]             order deliverydate asc, periodnumber asc, publishdate desc";             var parser = new tsql120parser(false);              ilist<parseerror> errors;             var fragment = parser.parse(new stringreader(sql), out errors);              var wherevisitor = new wherevisitor();             fragment.accept(wherevisitor);              //  have clauses in wherevisitor.wherestatements  class wherevisitor : tsqlconcretefragmentvisitor {     public readonly list<whereclause> wherestatements = new list<whereclause>();      public override void visit(whereclause node)     {         wherestatements.add(node);     }  } 

each of clauses in wherevisitor.wherestatements (3 in example) expose property called searchcondition. unfortunately, run out of ideas. want achieve logic per below:

foreach (var clause in wherevisitor.wherestatements) {     //  if part of clause filters based on curveid      //        capture curveids      //  end if } 

other details:

• using c# (.net 4.0)
• sql server 2008
• dll microsoft.sqlserver.transactsql.scriptdom (located in case @ 'c:\program files (x86)\microsoft sql server\130\tools\powershell\modules\sqlps\microsoft.sqlserver.transactsql.scriptdom.dll')

edit 1

some additional info:

• curveid key table. not make sense in case operate on (e.g. curveid+1 or curveid <= 10).

edit 2 (partial solution)

having following visitor helps case clause resembles curveid = 123:

class curveidvisitor : tsqlconcretefragmentvisitor {     public readonly list<int> curveids = new list<int>();      public override void visit(booleancomparisonexpression exp)     {         if (exp.firstexpression columnreferenceexpression && exp.secondexpression integerliteral )         {             //  there possibility of ilk 'curveid = 123'             //  'identifier'             //  take last if there multiple.  example:             //      alias.curveid             //  goives 2 identifiers: alias , curveid             if (                 ((columnreferenceexpression) exp.firstexpression).multipartidentifier.identifiers.last().value.tolower() ==                 "curveid")             {                 //  curveid filter                 //  find curve id                 int curveid = int.parse(((integerliteral) exp.secondexpression).value);                 curveids.add(curveid);             }         } 

finally solved , hope benefits else in future. perhaps else may read ni time , provide better solution.

public class sqlparser {     public list<int> getqueriedcurveids(string sql)     {         var parser = new tsql120parser(false);          ilist<parseerror> errors;         var fragment = parser.parse(new stringreader(sql), out errors);          list<int> curveids = new list<int>();         curveidvisitor cidv = new curveidvisitor();         inpredicatevisitor inpv = new inpredicatevisitor();         fragment.acceptchildren(cidv);         fragment.acceptchildren(inpv);          curveids.addrange(cidv.curveids);         curveids.addrange(inpv.curveids);         return curveids.distinct().tolist();     } }    class curveidvisitor : tsqlconcretefragmentvisitor {     public readonly list<int> curveids = new list<int>();      public override void visit(booleancomparisonexpression exp)     {         if (exp.firstexpression columnreferenceexpression && exp.secondexpression integerliteral )         {             //  there possibility of ilk 'curveid = 123'             //  'identifier'             //  take last if there multiple.  example:             //      alias.curveid             //  goives 2 identifiers: alias , curveid             if (                 ((columnreferenceexpression) exp.firstexpression).multipartidentifier.identifiers.last().value.tolower() ==                 "curveid")             {                 //  curveid filter                 //  find curve id                 int curveid = int.parse(((integerliteral) exp.secondexpression).value);                 curveids.add(curveid);             }         }     } }  class inpredicatevisitor : tsqlconcretefragmentvisitor {     public readonly list<int> curveids = new list<int>();      public override void visit(inpredicate exp)     {         if (exp.expression columnreferenceexpression)         {             if (                 ((columnreferenceexpression) exp.expression).multipartidentifier.identifiers.last().value.tolower() ==                 "curveid")             {                 foreach (var value in exp.values)                 {                     if (value integerliteral)                     {                         curveids.add(int.parse(((integerliteral)value).value));                     }                 }             }         }     } } 

this cut down code demonstrate answer. in real life, want check parseerror collection , add error handling!