Breaking a SQL statement down into his most elementary parts

• anthuenis_chris

  SSC Rookie

  Points: 49

  More actions

  September 17, 2008 at 8:45 am

  #75319

  We want to make a data dictionary of all our SQL statements.

  In this data dictionary we want to keep the relation : 'SQL statement uses these tables and these columns'.

  The problem starts when you have a more complex SQL statement than 'Select * FROM T1'

  To solve this problem we need 'something' (a parser, a user-program, ...) that turns the original SQL statement into his most elementary parts.

  An example :

  Input : select t1.f1, t2.f3 from t1, t2 where t1.f1 = t2.f1

  Output : 1st : select t1.f1 from t1 into tempdb as tx

  2nd : select t2.f1, t2.f3 from t2 into tempdb as ty

  3rd : merge tx and ty on f1

  Surely the SQL parser or SQL optimizer must do something like that to deliver the result.

  Is there a way we can capture that ?

  Is it possible to have something like that using VB or VB.net ?

  Thanks for any input that can help us.

• Sergiy

  SSC Guru

  Points: 110209

  More actions

  September 17, 2008 at 11:48 pm

  #873398

  Make views from most used joins.

  Then reuse those views in further queries/views.

  But be careful: no aggregations, no TOP 100 PERCENT, no data modifications in such views!

  Otherwise you kill system performance.

  _____________
  Code for TallyGenerator