August 16, 2013 at 11:18 pm
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
August 17, 2013 at 1:57 am
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/
August 17, 2013 at 1:58 am
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/
August 19, 2013 at 1:36 pm
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.
August 19, 2013 at 1:42 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 19, 2013 at 1:47 pm
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
August 19, 2013 at 1:50 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply