On January 27th, this site will be read-only as we migrate to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,573 Users
  • 2,269,760 Discussions
  • 7,916,783 Comments

Discussions

Discoverer EUL_US: SQL to list Workbooks with their associated Worksheets.

LPNO
LPNO Member Posts: 128 Blue Ribbon

I'm in charge of maintaining 200 workbooks with about 1000 worksheets. By exploring the tables structures of EUL_US and the web I collected and developed a few usefull SQL's to list all Workbooks ( table EUL_DOCUMENTS) with their associated Folders or views (I prefer to give the folders the same names as the views). Such this query is quite usefull:

select distinct d.doc_name, o.sobj_ext_table,o.obj_name from EUL_US.eul5_documents d , EUL_US.eul5_elem_xrefs x , EUL_US.eul5_expressions e , EUL_US.eul5_objs o where x.ex_from_type = 'DOC' and x.ex_from_id = d.doc_id and x.ex_to_id = e.exp_id and e.IT_OBJ_ID = o.obj_id; 

What I eagerly looking for now is a list of the relation workbook - worksheets. I analyzed the EUL_US tables, I search the web for all kinds of possible keyword sequences. But I didn't find anything listing the structure of this relation.

The closest match is, reading the statistics table EUL5_QPP_STATS

SELECTa.qs_doc_owner Owner,a.qs_doc_name Workbook,a.qs_doc_details Worksheet,count(*) run_countFROM EUL_US.eul5_qpp_stats a,EUL_US.eul5_documents bWHERE a.qs_created_date > '01-JAN-2011'  -- only recent reportsAND a.qs_doc_name = b.doc_nameGROUP BYa.qs_doc_owner,a.qs_doc_name,a.qs_doc_detailsORDER BY run_count DESC

Anyhow this is not really the right table, as it reports not about the object relation, but about the occured worksheet invocations.

Can you give me please a hint where I find the Workbook-Worksheet Relation?

Tagged:
Dragon777
This discussion has been closed.