Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Blin
Starting Member
36 Posts |
 Posted - 2005-09-01 : 15:06:19
|
| I'd like to know if there's a way to list all tables and views within a stored procedure.Thanks.Ben |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-01 : 15:19:59
|
This might tell you, but it will be a bit frgile - and won't tell you if the SProc references anything in another database etc.select O_DEP.[name], * from dbo.sysdepends D JOIN dbo.sysobjects O_SP ON O_SP.id = D.id AND O_SP.type = 'P' JOIN dbo.sysobjects O_DEP ON O_DEP.id = D.depidWHERE O_SP.[name] = 'MySProc' Kristen |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-09-01 : 15:30:21
|
| Nothing out of the box that I'm aware of. Kristen's could be a guide line but besides what he said it also will miss any objects that were created after the procedure. ie: if you script all your objects and some table scripts happened to run after the SP scripts that reference them, you'd miss those tables. As a completely over-the-top idea, you could parse the procedure text from information_schema.routines (or syscomments) using any one of the bazillion splitting/parsing functions that have been posted here and inner join the results to information_schema.tables to get a distinct list table/view names.Be One with the OptimizerTG |
|
|
|
Blin
Starting Member
36 Posts |
Posted - 2005-09-01 : 15:39:59
|
| Thanks. Here's another question:How can I find out what stored procedure is called by a SQL Server Agent job? |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-09-01 : 15:44:38
|
| Well, you just look at the job steps from Enterprise Manager. But I guess you'd have to examine the called procs to see if they in tern exec other procs from within.Is that what you mean?Be One with the OptimizerTG |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-01 : 15:45:00
|
| SQL Profiler?Kristen |
|
|
|
Blin
Starting Member
36 Posts |
Posted - 2005-09-01 : 19:19:37
|
| I meant writing a script to list the stored procedure name for the job scheduled. Does anyone know how to do it? |
|
|
|
|
|
|
Dependency