tables accessed in a stored proc!

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

tables accessed in a stored proc!

Author

Topic

xpandre
Posting Yak Master

212 Posts

Posted - 2002-07-17 : 04:43:02

Hi,
is there a possible way to find out all the tables accessed in a stored proc?
Thank you
sam

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-17 : 07:44:11

You can run sp_depends on the stored procedure, it will list all the objects that it depends upon (and which depend upon it!)

xpandre
Posting Yak Master

212 Posts

Posted - 2002-07-17 : 07:52:54

hi
thanx!!
1 problem though!!

i am using the BCP utility to get it written to a text file.Now when i use this query in the master database,it wont give me the column name ..but just the table name of the target database

select (s6.name+'.' + o1.name),
col_name(d3.depid, d3.depnumber)
from dbo.sysobjects o1
,master.dbo.spt_values v2
,dbo.sysdepends d3
,master.dbo.spt_values u4
,master.dbo.spt_values w5 --11667
,dbo.sysusers s6
where o1.id = d3.depid
and o1.xtype = substring(v2.name,1,2) collate database_default and v2.type = 'O9T'
and u4.type = 'B' and u4.number = d3.resultobj
and w5.type = 'B' and w5.number = d3.readobj|d3.selall
and d3.id =(select object_id('proc_name'))
and o1.uid = s6.uid
and deptype < 2

can any1 tell me wher i mite be going wrong?
thank you
sam

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-17 : 07:57:31

I don't understand what you mean about "it won't give me the column name".

xpandre
Posting Yak Master

212 Posts

Posted - 2002-07-17 : 08:00:50

sorry!!:-)

it gives me the result such when i execute this from same schema(database) inwhich the table exists..it gives me

tableabc a1
tableabc a2
tableabc a3
tableabc a4

while if i execute this sql from the master schema(database) it gives

tableabc NULL
tableabc NULL
tableabc NULL
tableabc NULL

hope u got the problem!!
thanx
Sam

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-17 : 08:20:47

sysdepends, sysusers, and sysobjects are local to the current database, that's why if you run it from master, it will use the master..sysobjects, not the other database's sysobjects. You'd have to run "USE db" before you can execute your query.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources