EcalCondDB/sql/dumpRelationShips.sql

0001 /*
0002
0003   This script generates two files with a complete description of tables
0004   and their foreign key relationships
0005
0006   Author: Giovanni.Organtini@roma1.infn.it 2011
0007
0008 */
0009
0010 SET NEWPAGE 0
0011 SET SPACE 0
0012 SET LINESIZE 250
0013 SET PAGESIZE 0
0014 SET ECHO OFF
0015 SET FEEDBACK OFF
0016 SET HEADING OFF
0017 SET MARKUP HTML OFF
0018 SET LONG 90000;
0019
0020 column parent format a30
0021 column child format a30
0022 column column_name format a30
0023
0024 /* tables */
0025 SPOOL TABLES.DAT
0026 SELECT T.TABLE_NAME, COLUMN_NAME FROM USER_TABLES T JOIN USER_TAB_COLUMNS C ON
0027 T.TABLE_NAME = C.TABLE_NAME ORDER BY TABLE_NAME;
0028 SPOOL OFF
0029
0030 /* constraints */
0031 SPOOL RELATIONSHIPS.DAT
0032 SELECT a.table_name parent, a.column_name, c.table_name child, c.column_name
0033 FROM user_cons_columns a,user_cons_columns c,user_constraints b
0034 WHERE a.constraint_name=b.constraint_name
0035 AND a.table_name=b.table_name
0036 AND b.constraint_type='R'
0037 AND b.r_constraint_name=c.constraint_name
0038 ORDER by a.table_name;
0039 SPOOL OFF
0040