tables in stored procedures

[Former Member]

Hi All,

I am trying to find tables used by the stored procedures.Manually it takes a lot of time.

Would really appreciate if someone can provide sample sql query to search tables in stored procs.

Thanks,

Kiran

[lbreddemann · Accepted solution]

Kiran,

you may want to use system view sys.object_dependencies.

I have a table 'AAA' that I used in several models and procedures:

select * from  "SYS"."OBJECT_DEPENDENCIES" 
where base_object_name ='AAA'

BASE_SCHEMA_NAME	BASE_OBJECT_NAME	BASE_OBJECT_TYPE	DEPENDENT_SCHEMA_NAME	DEPENDENT_OBJECT_NAME	DEPENDENT_OBJECT_TYPE	DEPENDENCY_TYPE
LARS	AAA	TABLE	_SYS_BIC	lars/AV_TEST2/olap	VIEW	0
LARS	AAA	TABLE	_SYS_BIC	nr/AN_NR_CC/olap	VIEW	0
LARS	AAA	TABLE	_SYS_BIC	nr/AN_NR_CC	VIEW	0
LARS	AAA	TABLE	_SYS_BIC	lars/AV_TEST2	VIEW	0
LARS	AAA	TABLE	_SYS_BIC	lars/AV_TEST2/FLAG/hier/FLAG	VIEW	0
LARS	AAA	TABLE	_SYS_BIC	lars/AV_TEST2/COLA/hier/COLA	VIEW	0
LARS	AAA	TABLE	_SYS_BIC	lars/AV_TEST2/COLB/hier/COLB	VIEW	0
LARS	AAA	TABLE	_SYS_BIC	lars/AV_TEST2/ca/hier/ca	VIEW	0
LARS	AAA	TABLE	_SYS_BIC	nr/AN_NR_CC/FLAG/hier/FLAG	VIEW	0
LARS	AAA	TABLE	_SYS_BIC	nr/AN_NR_CC/COLA/hier/COLA	VIEW	0
LARS	AAA	TABLE	_SYS_BIC	nr/AN_NR_CC/COLB/hier/COLB	VIEW	0
LARS	AAA	TABLE	_SYS_BIC	nr/AN_NR_CC/ca/hier/ca	VIEW	0
LARS	AAA	TABLE	LARS	TESTPROC	PROCEDURE	1
LARS	AAA	TABLE	LARS	NO_LIMIT	PROCEDURE	1

That should do the trick for your requirement.

Cheers, Lars

[Former Member · Accepted solution]

Thanks Lars!

It was really very helpful.