Wednesday, September 18, 2013

SQL Developer: add a "child tables" tab to table definition

I always like to extend SQL Developer's table definition by adding a tab that shows the "child tables" for each table - i.e. the tables that have a foreign key to the table in context.

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 in (select pk.constraint_name
                                                    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>

6 comments:

carpenteri said...

Superb tip Tony, I wasn't of this feature. Thanks also for posting the link to Sue's original post.

Cheers

Ian

Gopinadha Swamy Upadhyayula said...

Hi Tony, is there a way to find the Parent tables as well?

Gopinadha Swamy Upadhyayula said...

Hi Tony, is there a way to see the Parent tables as well, for a given table?

Tony Andrews said...

@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?

Gopinadha Swamy Upadhyayula said...

@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>

Gopinadha Swamy Upadhyayula said...

@Tony, never mind. I found the issue myself. Stupid typo. I missed additional ] at the end, in the second XML. It is resolved now.