October 30, 2013 at 2:46 am
Hi All,
I want to know the tables used inside a Stored Procedure. For ex,
CREATE TABLE TABLEB(
X INT
)
CREATE TABLE TABLEC(
Y INT
)
CREATE PROC usp_TEST
AS
INSERT INTO TABLEB VALUES(1)
SELECT Y FROM TABLEC
EXEC usp_TEST
I want to know "Objects on which usp_TEST depends on as TABLEB and TABLEC via a script(as I'm having many stored procedure so viewing through view dependencies is a challenge). I'm looking for a script where I give a storedprocedure name as input and get to know the Tables it depends on.
Please suggest.
Regards
Jim
October 30, 2013 at 4:08 am
You can use this query to find all the tables used in a stored procedure.
;WITH stored_procedures AS (
SELECT
o.name AS proc_name, oo.name AS table_name,
ROW_NUMBER() OVER(partition by o.name,oo.name ORDER BY o.name,oo.name) AS row
FROM sysdepends d
INNER JOIN sysobjects o ON o.id=d.id
INNER JOIN sysobjects oo ON oo.id=d.depid
WHERE o.xtype = 'P')
SELECT proc_name, table_name FROM stored_procedures
WHERE row = 1-- and proc_name like 'usp_test'(optional)
ORDER BY proc_name,table_name
October 30, 2013 at 4:34 am
Thanks. But the challenge is, say TABLEB is dropped from the database and it is still used in the procedure. So if I compile the procedure, it will not throw any error but when it executes at that time it throws the error.
I want to identify those tables which are not there in the DB and used in the procs. So I thought to view dependency and get the tables and check the object exists. But my fault, once it is dropped from DB even view dependency will lose that object even it is there in the procedure.
Is there any way to know those tables which are there in the Proc but not in DB?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply