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 Procedure names with table names referenc
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

yougandhar1
Starting Member

USA
7 Posts

Posted - 06/10/2013 :  09:13:13  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 06/10/2013 :  09:20:37  Show Profile  Reply with Quote
;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
52325 Posts

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

yougandhar1
Starting Member

USA
7 Posts

Posted - 06/10/2013 :  11:10:01  Show Profile  Reply with Quote
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

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

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

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

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

yougandhar1
Starting Member

USA
7 Posts

Posted - 06/11/2013 :  09:29:15  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 06/11/2013 :  09:33:08  Show Profile  Reply with Quote
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
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