SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Retrieve Table names referenced in a Stored Proced
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

yougandhar1
Starting Member

USA
7 Posts

Posted - 06/18/2013 :  08:29:47  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3657 Posts

Posted - 06/18/2013 :  08:34:11  Show Profile  Reply with Quote
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.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 06/18/2013 :  08:37:17  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/18/2013 :  10:19:10  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 06/19/2013 :  00:27:10  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/19/2013 :  01:00:20  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000