November 29, 2010 at 3:55 pm
Never mind. I had too much turkey over Thanksgiving. Since the store procedures are all in the master database and the tables are not, it will not show the dependencies. (is this year over???) 😉
Does anyone know an easy way (other than reading it line by line) to get a list of all tables being used in a stored procedure? I am using the below script, in 2000 the depnumber of 1 gave table names and depnumber 2 gave stored procedure names; howver in 2005 this is not working for depnumber 1. When I run the script, I do get the stored procedures but no tables. Can anyone know why? Or how to get the table list?
SELECT DISTINCT OBJ.id,
OBJ.name AS 'Procedure_Name',
CASE WHEN DEP.depnumber = 1 THEN JBO.name
ELSE '' END AS 'Table_Name',
CASE WHEN DEP.depnumber = 0 THEN JBO.name
ELSE '' END AS 'SP_Name',
DEP.depid,
DEP.depnumber
FROM master.dbo.sysdepends AS DEP
INNER JOIN master.dbo.sysobjects AS OBJ
ON DEP.id = OBJ.id
INNER JOIN master.dbo.sysobjects AS JBO
ON DEP.depid = JBO.id
WHERE OBJ.name = 'storedprocedurename'
Thanks,
June 3, 2011 at 10:41 am
Dan,
I had the same question. Did you find the solution yet?
Mr.P
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply