BorlandTalk.com

Roger Venad · Guest

Could someone tell me if there exists a function that I can use with dbexpress to find the referential Integrity of a table which resides in Oracle, SQL Server or AS400.
Thanks in advance.
Roger Venad

Craig Stuntz [TeamB] · Guest

Roger Venad wrote:

Michel Sabourin · Guest

Roger,

On iSeries (AS/400), you can use the following query on system SQL views to
extract constraints related to <YourTable> in <YourSchema>:

SELECT CONSTRAINT_NAME
FROM SYSREFCST
WHERE UNIQUE_CONSTRAINT_SCHEMA CONCAT UNIQUE_CONSTRAINT_NAME IN (

SELECT CONSTRAINT_SCHEMA CONCAT CONSTRAINT_NAME
FROM SYSCST
WHERE TABLE_NAME = '<YourTable>'
AND TABLE_SCHEMA = '<YourSchema>'
AND CONSTRAINT_TYPE IN ('PRIMARY KEY', 'UNIQUE'))
UNION
SELECT CONSTRAINT_NAME
FROM SYSCST
WHERE TABLE_NAME = '<YourTable>'
AND TABLE_SCHEMA = '<YourSchema>'
ORDER BY CONSTRAINT_NAME;

Alternatively, you can access underlying system physical tables, in this
case to display the complete picture of constraints related to <YourTable>
in <YourSchema>:
select cst.DBCCNM as Constraint, DBCCTY as Type,
DBCCKC KEY,
substr(trim(cst.DBCCFL) CONCAT '/' CONCAT trim(DBCCFF), 1, 16) as Child,
substr(trim(cst.DBCPFL) CONCAT '/' CONCAT trim(DBCPFF), 1, 16) as Parent,
DBCRDR as ON_DELETE , DBCRUR as ON_UPDATE ,
substr(char(DBCTMS), 1, 10) as date,
substr(char(DBCTMS), 12, Cool

as time,
DBCCDN as condition, DBCCWK as field_order
from qadbfcst as CST
LEFT OUTER JOIN QADBCCST AS KEY
on key.DBCCKL = cst.DBCCNL AND
key.DBCCKN = cst.DBCCNM
where (DBCCFF = '<YourTable>' AND DBCCFL = '<YourSchema>') OR
(DBCPFF = '<YourTable>' AND DBCPFL = '<YourSchema>')
ORDER BY CONSTRAINT, field_order ;

Regards,
Michel Sabourin

"Craig Stuntz [TeamB]" <cstuntz (AT) nospam (DOT) please [a.k.a. vertexsoftware.com]> a
écrit dans le message de news:3fc21c10 (AT) newsgroups (DOT) borland.com...