Post reply

Generate Query Plans to find column dependencies

  • April 7, 2008 at 5:47 am

    #184262

    Hi,

    Is there anyway of generating query plans for all procedures, and loading them into a table, so that I can analyze column dependencies within stored procedures? This is not being used for performance tuning.

    I need to do this without having to pass parametres to all the stored procs.

    I cannot rely on syscomments to help me do this, as a search for 'SELECT *' does not provide me with a column list.

    Are there any VB.net classes/methods which generate query execution plans?

    At the moment we have a mix of SQL Server 2000/2005 and I need to perform this task on some SQL 2000 servers, otherwise I believe this can be performed relatively easily using XML Query plans.

    Any help or advice kindly accepted.

    Thanks

    Paul

  • April 7, 2008 at 6:45 am

    #799071

    Not quite sure if it will help but you can use sp_helptext procedurename to get the text of the procedure, so you could loop through all the procedures select name from sysobjects

    where xtype = 'p' and pull out all the text.

    Facts are stubborn things, but statistics are more pliable - Mark Twain
    Carolyn
    SQLServerSpecialists[/url]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply