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

回答1:

You can use SHOWPLAN_ALL setting and parse the output.

回答2:

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

回答3:

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

Thank you all for your responses.

回答4:

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

回答5:

sp_depends should help

回答6:

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.