Retrieve Table names referenced in a Stored Proced

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

General SQL Server Forums

New to SQL Server Programming

Retrieve Table names referenced in a Stored Proced

Author

Topic

yougandhar1
Starting Member

7 Posts

Posted - 2013-06-18 : 08:29:47

Hello All,

I am trying to retrieve table names referenced in a Stored procedure in PL/SQL. I have tried the following from previous help on here

1) SELECT referenced_schema_name, referenced_entity_name, referenced_minor_name,
referenced_minor_id, referenced_class_desc, is_caller_dependent, is_ambiguous FROM sys.dm_sql_referenced_entities ('PRC_BISYS_AP_PROCESS', 'OBJECT')

ERROR: It says the statement has not ended correctly. I have tried using semicolons at the end ; , Still the same error,

2);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
ORDER BY proc_name,table_name

ERROR: Invalid character and I deleted the ";" in the beginning and tried then comes up with an error message as FROM keyword not found where expected. and the cursor takes me to "AS row" in line 5,

Any help is appreciated--

Thx

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-18 : 08:34:11

This forum is for Microsoft SQL Server, so expertise on PL/SQL would be rare. You might try posting to an Oracle forum or a more generalized database forum.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-18 : 08:37:17

Hi Yougandhar,
This code is working fine in my EDITOR (SSMS)

;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
ORDER BY proc_name,table_name

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-18 : 10:19:10

quote:

Originally posted by bandi

Hi Yougandhar,
This code is working fine in my EDITOR (SSMS)

;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
ORDER BY proc_name,table_name

--
Chandu

I think issue is OP is on PL/SQL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-19 : 00:27:10

quote:
Originally posted by visakh16
I think issue is OP is on PL/SQL

@Visakh, There is no sysobjects view in PL/SQL (Oracle)....

@Yougandhar, On which editor you are executing that query?

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-19 : 01:00:20

quote:
Originally posted by bandi

quote:
Originally posted by visakh16
I think issue is OP is on PL/SQL

@Visakh, There is no sysobjects view in PL/SQL (Oracle)....

@Yougandhar, On which editor you are executing that query?

--
Chandu

Thats why i told

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources