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.
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 rowFROM sysdepends d INNER JOIN sysobjects o ON o.id=d.idINNER JOIN sysobjects oo ON oo.id=d.depidWHERE o.xtype = 'P')SELECT proc_name, table_name FROM stored_proceduresWHERE row = 1ORDER 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_proceduresWHERE row = 1ORDER 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_proceduresWHERE row = 1ORDER BY proc_name,table_name --Chandu
I think issue is OP is on PL/SQL------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 visakh16I 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 visakh16I 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|