I have lost count of how many times I have started work at a new environment or on a new PC and had to set this up from scratch, so thought I'd document it here for next time!
First, create a file containing the following XML:
<items>
<item type="editor" node="TableNode" vertical="true">
<title><![CDATA[Child Tables]]></title>
<query>
<sql><![CDATA[select cons.table_name, cons.constraint_name
from all_constraints cons
where cons.constraint_type = 'R'
and (cons.r_constraint_name, cons.r_owner) in
(select pk.constraint_name, pk.owner
from all_constraints pk
where owner = :OBJECT_OWNER
and table_name = :OBJECT_NAME
and constraint_type in ('P','U'))]]>
</sql>
</query>
</item>
</items>
Then, in SQL Developer go to Tools->Preferences... and open the Database node and select user Defined Extensions.
Click Add Row and on the new row set Type to EDITOR and Location to point to your XML file.
That's it. You may just need to restart SQL Developer to make it work.
I am indebted to Sue Harper's blog for the detailed instructions on adding tabs to SQL Developer.
Another tab I like to add is one to show the errors for an invalid view definition:
<items>
<item type="editor" node="ViewNode" vertical="true">
<title><![CDATA[ERRORS]]></title>
<query>
<sql><![CDATA[SELECT
ATTRIBUTE, LINE
|| ':'
||POSITION "LINE:POSITION", TEXT
FROM
All_Errors
WHERE
type = 'VIEW'
AND owner = :OBJECT_OWNER
AND name = :OBJECT_NAME
ORDER BY
SEQUENCE ASC
]]></sql>
</query>
</item>
</items>
(Update 2019: SQL Developer now includes an Errors tab for views already, so this is redundant.)
6 comments:
Superb tip Tony, I wasn't of this feature. Thanks also for posting the link to Sue's original post.
Cheers
Ian
Hi Tony, is there a way to find the Parent tables as well?
Hi Tony, is there a way to see the Parent tables as well, for a given table?
@Gopinadha, you could write a similar parent tables query as well, by reversing the logic of the child tables query. But is there any need to, since you can find the parent tables already by looking at the foreign keys in the Constraints tab?
@Tony, Ofcourse there is no need to have parent tables query. But, it looks nice without having other constraints. Pure and simple parent table references information loos good. But, it should work. Right? When i run the second query in SQL book by replacing the OBJECT_NAME & OBJECT_OWNER with an example table name & schema name, it returns the expected results. But, Only when adding it to the User defined extensions, it doesn't even show the next tab. I can see the first tab named "Child Tables" but not the second one named "Parent Tables".
Here is the first xml & i named it Child_table_References.xml.
<items>
<item type="editor" node="TableNode" vertical="true">
<title><![CDATA[Child Tables]]></title>
<query>
<sql>
<![CDATA[select a.owner,
a.table_name,
a.constraint_name,
a.status
from all_constraints a
where a.constraint_type = 'R'
and exists(
select 1
from all_constraints
where constraint_name=a.r_constraint_name
and constraint_type in ('P', 'U')
and table_name = :OBJECT_NAME
and owner = :OBJECT_OWNER)
order by table_name, constraint_name]]>
</sql>
</query>
</item>
</items>
Here is the second xml & i named it Parent_table_References.xml.
<items>
<item type="editor" node="TableNode" vertical="true">
<title><![CDATA[Parent Tables]]></title>
<query>
<sql>
<![CDATA[SELECT C.table_name CHILD_TABLE,
P.table_name PARENT_TABLE,
C.owner CHILD_TABLE_OWNER,
P.owner PARENT_TABLE_OWNER
FROM dba_constraints P,
dba_constraints C
WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
AND C.constraint_type = 'R'
AND P.CONSTRAINT_NAME = C.R_CONSTRAINT_NAME
AND C.TABLE_NAME = :OBJECT_NAME
AND C.owner =:OBJECT_OWNER]>
</sql>
</query>
</item>
</items>
@Tony, never mind. I found the issue myself. Stupid typo. I missed additional ] at the end, in the second XML. It is resolved now.
Post a Comment