Need to list all columns,table names used in T-SQL Query

  • Hi All,

    Recently we are planning to do some schema level changes to few tables, so now i have to find out all the depended scripts/SSRS reports/SSIS packages which are using those columns.

    we have around 60 reports and around 20+ T-SQL scripts.

    As its regular task as part of dev cycle my manager asked me to create report with all Object References to packages/SSRS

    To be more clear the Object references report will have below fields

    BI Type Name TableNames ColumnNames

    SSRS Report1 SimpleTable1 Col1,Col2,COl3

    SSRS Report1 SimpleTable2 COl4,Col5,COl7

    SSIS Package1 Table5 Col6,Col1

    Please let me know if there is any automated way to find reference fileds.

    I know there is way to find out related tables in a Stored procedure but is there any way i can find column names also ?

    Regards,

    Krish

  • That's going to be quite a challenge if it is possible at all.

    You might want to look at this recent thread: http://www.sqlservercentral.com/Forums/Topic1351799-392-1.aspx

    Look for the suggestion by Eugene Elutin to generate and parse the XML query plan. It may be possible to do what you want to do that way, then again maybe not.

    And by all means, pay homage to Mona at the end. 🙂


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Duplicate post. Please post all further responses here.

    Please, don't make duplicate posts, it just fragments your answers to your questions. Most people that help here either subcribe to specific forums, or use the Recent Posts or Active Threads links to monitor questions.

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

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