Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

query to list all the table names used in stored procedure Expand / Collapse
Author
Message
Posted Friday, August 16, 2013 11:18 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 28, 2014 12:55 PM
Points: 16, Visits: 64
hi i want to make a query to list down all the tables used in stored procedure. plz suggest

for example my stores proc has three tables it should list as below:

sp_name table_name
proc1--------table1
proc1---------tablle2
proc1---------table3
Post #1485434
Posted Saturday, August 17, 2013 1:57 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
you can refer this link-

http://www.sqlservercentral.com/scripts/Maintenance+and+Management/31896/



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1485452
Posted Saturday, August 17, 2013 1:58 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
one more link of forum:

http://www.sqlservercentral.com/Forums/Topic401606-444-1.aspx



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1485454
Posted Monday, August 19, 2013 1:36 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 28, 2014 12:55 PM
Points: 16, Visits: 64
thnks for help but now i need to modify the same query this query only shows the tables from one database but i need it to run across different database plz suggest the same.
Post #1485976
Posted Monday, August 19, 2013 1:42 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:05 PM
Points: 13,137, Visits: 11,975
raj.prabhu001 (8/19/2013)
thnks for help but now i need to modify the same query this query only shows the tables from one database but i need it to run across different database plz suggest the same.


Are you saying you want to run this same script on all databases on your server? This is one of those rare times where a cursor is exactly what you need. One possibility is to use the undocumented sp_msforeachdb. A quick search will present you with examples. The other option is to roll your own cursor with dynamic sql.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1485980
Posted Monday, August 19, 2013 1:47 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 28, 2014 12:55 PM
Points: 16, Visits: 64
yes i hv two database db1 and db2 and my proc uses tables from the both database and i need to find them .

can u plz suggest the query. thanks
Post #1485983
Posted Monday, August 19, 2013 1:50 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:05 PM
Points: 13,137, Visits: 11,975
raj.prabhu001 (8/19/2013)
yes i hv two database db1 and db2 and my proc uses tables from the both database and i need to find them .

can u plz suggest the query. thanks


How about the query suggested above? Then run it on both databases.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1485985
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse