SQL Table and column Parser for stored procedures

Question

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 regular expression but its not 100% reliable.

Answer 1

• 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)

Answer 2

You could call the stored procedures programmatically (in a development environment) and get the resulting columns. Maybe you have a naming convention in order to avoid calling insert and update procedure. You'll have to find way to set the right parameters too.

Note: I think a 100% reliable solution is technically impossible, because of the way stored procedure (can) work.

Look at this example:

[...]
@MyDate datetime

AS

    IF (day(@MyDate) = 1)
    BEGIN
        SELECT * FROM MyFirstTable
        RETURN
    END

    IF (@MyDate > getdate())
        SELECT MyID, MyText FROM MySecondTable WHERE ADate > @MyDate
    ELSE
        EXEC Other_StoredProcedure @MyType, @MyDate

So there are two problems: the resulting columns could differ and you'll have to follow recursively other stored procedures.

Answer 3

You can use SHOWPLAN_ALL setting and parse the output.

Answer 4

sp_depends should help

Answer 5

Decided to create an small application using Regex to satisfy my current needs.

Thank you all for your responses.

Answer 6

sp_depends Worked perfectly for me. It showed what table or SP might be affected by my changes