Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2010
    Posts
    2

    Unanswered: How to get the list of tables in a stored Procedure in SQL Server 2005

    Hi All,

    I want to get a list of all tables with in a stored procedure. I have a lot of stored procedures, some of them are obsolete and hence we want to remove the Sp and some old tables. We have a list of all active SPs. Based on this we want to see what are the tables used in these SPs. Checking it manually is very tedious. Is there an alternate way where i can get the list of tables used for a stored proc or number of stored procs.

    Thanks,
    Nitesh

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Not if you are using dynamic SQL within any of your procedures. Otherwise, you can query the system tables for dependencies.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    syscomments.text
    sys_sqlmodules.definition

    yes, dynamic sql makes things difficult but not impossibly hard.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    And how would you do it with a dynamic SQL environment and still be able to guarantee that you did not drop any necessary tables?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •