Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Table and column Parser for stored procedures

Is there an application , which can parse a given set of stored procedures (SQL Server 2000) and gets all tables and associated columns that are being used in it. The stored procedure can have tables from different databases.

Output should be like TableA columnA columnC columnD

TableB columnE columnF columnG

I have written an small application using Database Edition GDR Any one interested can refer to http://tsqlparsergdr.codeplex.com

like image 331
rsapru Avatar asked Oct 15 '22 18:10

rsapru


1 Answers

  • UPDATE - 20 Jan 2009 *
  • Visual Studio DB Professional Edition shipped with a full T-SQL script DOM parser
  • http://blogs.msdn.com/gertd/archive/2008/08/21/getting-to-the-crown-jewels.aspx

Not a concrete solution - but a line of thought.

Considered sysdepends as a potential solution - but it is notoriously unreliable at containing all the dependent object information.

but how about something Lex/Yacc derived? There are a few commercial parsers e.g.

http://www.sqlparser.com/download.php

Not looked for open-source implementation but I think I would look for that route. Here's how I started my search:

http://www.google.com/search?hl=en&q=sql+lex+yacc+parse

BNF syntax for ANSI SQL can be found here:

http://savage.net.au/SQL/

With a lex implementation of choice, this seems a relatively straight forward engineering problem from here. (albeit with some heavylifting if you want to support MS SQL extensions)

like image 55
stephbu Avatar answered Oct 27 '22 22:10

stephbu