translate an Oracle query

[Former Member]

I have an Oracle query which I need to translate into SQL Anywhere 17. Here is the Oracle query:

SELECT a.table_name || '|' || a.column_name    AS Reference,
       a.table_name                            AS TableName,
       a.column_name                           AS ColName
  FROM user_cons_columns a
       JOIN user_constraints c
         ON a.owner = c.owner
        AND a.constraint_name = c.constraint_name
       JOIN user_constraints c_pk
         ON c.r_owner = c_pk.owner
        AND c.r_constraint_name = c_pk.constraint_name
 WHERE c.constraint_type = 'R'
   AND c_pk.table_name = 'S1_DOCUMENT_TEMPLATE'
/

What are the equivalent tables/views for: user_cons_columns and user_constraints??

Thanks Murray

[VolkerBarth · Accepted solution]

Here's my attempt:

select TF.table_name || '|' || TC.column_name as REFERENCE,
   TF.table_name as TABLENAME,
   TC.column_name as COLNAME
   -- In case you have foreign tables with several FKs to the same parent table
   -- or have FKs with more than one column, you will usually also include
   -- X.index_name as role_name
   -- or group by TF.table_name, X.index_name to list all the columns
   -- that build one FK...
from SYS.SYSTAB TP
   inner join SYS.SYSFKEY FK on TP.table_id = FK.primary_table_id
   inner join SYS.SYSTAB TF on FK.foreign_table_id = TF.table_id
   inner join SYSIDX X on FK.foreign_table_id = X.table_id and FK.foreign_index_id = X.index_id
   inner join SYSIDXCOL XC on X.table_id = XC.table_id and X.index_id = XC.index_id
   inner join SYS.SYSTABCOL TC on XC.table_id = TC.table_id and XC.column_id = TC.column_id
where TP.table_name = 'S1_DOCUMENT_TEMPLATE'
order by REFERENCE

[Former Member · Accepted solution]

Thanks Volker - that works perfectly 🙂

[Former Member]

I tried this SQL:

SELECT a.table_name + '|' + c.column_name      AS Table_Reference,
       a.table_name                            AS TableName,
       c.column_name                           AS ColName
  FROM systable a, 
       syscolumn c
 WHERE c.table_id = systable.table_id
   AND systable = 'S1_DOCUMENT_TEMPLATE'

but get this error:

Correlation name 'systable' not found.

However this query works: select * from systable where table_name = 'S1_DOCUMENT_TEMPLATE';

[Former Member]

updated SQL:

SELECT a.table_name + '|' + c.column_name AS Table_Reference, a.table_name AS TableName, c.column_name AS ColName FROM systable a, syscolumn c WHERE c.table_id = a.table_id AND a.table_name = 'S1_DOCUMENT_TEMPLATE'

[VolkerBarth]

So is this still an open answer?

[Former Member]

Volker: it is still an open question. Here is the expected results:

REFERENCE
TABLENAME
COLNAME
-----------------------------------------
S1_CONTRACT|TEMPLATE_ID                                                                                  S1_CONTRACT                    
TEMPLATE_ID  
                                                                                            S1_VENDOR_DATA|PC_CONTRACT_TEMPLATE_ID                                                                   S1_VENDOR_DATA                 
PC_CONTRACT_TEMPLATE_ID

S1_CUSTOMER_DATA|SC_CONTRACT_TEMPLATE_ID                                                                 S1_CUSTOMER_DATA               
SC_CONTRACT_TEMPLATE_ID  
                                                                                S1_PLC_DATA|SC_CONTRACT_TEMPLATE_ID                                                                      S1_PLC_DATA                    

SC_CONTRACT_TEMPLATE_ID
                                                                                  S1_PLC_DATA|PC_CONTRACT_TEMPLATE_ID                                                                      S1_PLC_DATA                    
PC_CONTRACT_TEMPLATE_ID 
                                                                                 S1_GRAINSMART_OPTION|SC_CONTRACT_TEMPLATE_ID
S1_GRAINSMART_OPTION           
SC_CONTRACT_TEMPLATE_ID
                                                                                  S1_GRAINSMART_OPTION|PC_CONTRACT_TEMPLATE_ID                                                             S1_GRAINSMART_OPTION           
PC_CONTRACT_TEMPLATE_ID
                                                                                  S1_ORDERLOG_LOADING_INSTR|DEFAULT_TEMPLATE                                                               S1_ORDERLOG_LOADING_INSTR      
DEFAULT_TEMPLATE
                                                                                         S1_ADVANCED_SHIPMENT_MGM_DOC|DEFAULT_TEMPLATE                                                                                                                                                   
S1_ADVANCED_SHIPMENT_MGM_DOC   
DEFAULT_TEMPLATE                                                                                         9 rows selected. 

In other words, any time the table 's1_document_template' is the parent table in a Foreign Key relationship report the following: table_name '|' column_name table_name column_name

Our intent is to place this SQL in a view so that any future additional tables will be reported in the view automatically.

Murray