Retrieve Procedure names with table names referenc

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 Procedure names with table names referenc

Author

Topic

yougandhar1
Starting Member

7 Posts

Posted - 2013-06-10 : 09:13:13

Hi,

I am trying to retrieve the procedure names along with the tables referenced for WHOLE SCHEMA in one query.

I tried using dba_dependencies, but the results are not the way I wanted. Also is this code the same if you are coding in Oracle SQL Developer too?

Thanks,
Reddy

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-10 : 09:20:37

[code];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[/code]

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-10 : 10:12:42

use sys.dm_sql_referenced_entities
see

http://visakhm.blogspot.in/2010/01/finding-cross-server-cross-db-object.html

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

yougandhar1
Starting Member

7 Posts

Posted - 2013-06-10 : 11:10:01

Thank you Chandu for the code. But its giving me an error "From key word not found where expected"

Visakh,

Can you give me a sample code if you don't mind me asking?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-10 : 12:47:35

quote:
Originally posted by yougandhar1

Thank you Chandu for the code. But its giving me an error "From key word not found where expected"

Visakh,

Can you give me a sample code if you don't mind me asking?

Thanks

see example usage here


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 ('your procedure name here', 'OBJECT')

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-11 : 03:18:50

quote:
Originally posted by yougandhar1

Thank you Chandu for the code. But its giving me an error "From key word not found where expected"

Same query is working fine for me... Have you tried exact query or changed any thing ?


;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

yougandhar1
Starting Member

7 Posts

Posted - 2013-06-11 : 09:29:15

Hey Chandu, I have tried the query with out changing in Oracle SQL Developer. I got an error. I will try this in SQL Developer and see how that goes.

Thanks for your help--

Reddy

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-11 : 09:33:08

quote:
Originally posted by yougandhar1

Hey Chandu, I have tried the query with out changing in Oracle SQL Developer. I got an error. I will try this in SQL Developer and see how that goes.
Thanks for your help--
Reddy

This is SQL Server forum, not the Oracle SQL Developer.....
We gave query for SQL Server
You try your luck with Oracle related forums

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources