PROMPT ‘—————————–
PROMPT ‘– SCRIPT PARA RECUPERACAO DAS COLUNAS REFERENTE AS CONSTRAINTS DAS TABELAS DO AOL –‘
PROMPT ‘—————————–
COLUMN APPLICATION_SHORT_NAME FORMAT A4
COLUMN TABLE_NAME FORMAT A30
COLUMN PRIMARY_KEY_NAME FORMAT A30
COLUMN COLUMN_NAME FORMAT A27
COLUMN PKS FORMAT 999
COLUMN FKS FORMAT 999
SET LINES 400
SELECT FA.APPLICATION_SHORT_NAME
,FT.TABLE_NAME
,FPK.PRIMARY_KEY_NAME
,FPK.PRIMARY_KEY_TYPE
,FC.COLUMN_NAME
,FPKC.PRIMARY_KEY_SEQUENCE PKS
,FT2.TABLE_NAME
,FFK.FOREIGN_KEY_NAME
,FFK.FOREIGN_KEY_RELATION
,FC2.COLUMN_NAME
,FFKC.FOREIGN_KEY_SEQUENCE FKS
FROM FND_APPLICATION FA ,
FND_TABLES FT ,
FND_PRIMARY_KEYS FPK ,
FND_PRIMARY_KEY_COLUMNS FPKC ,
FND_COLUMNS FC ,
FND_FOREIGN_KEYS FFK ,
FND_FOREIGN_KEY_COLUMNS FFKC ,
FND_COLUMNS FC2 ,
FND_TABLES FT2
WHERE FA.APPLICATION_ID = FT.APPLICATION_ID
—
— PARA IDA UTILIZE FT E PARA VOLTA UTILIZE FT2
—
AND FT2.TABLE_NAME = UPPER(‘&Tabela_Desejada’)
—
— ATE AQUI EU DEFINI SOMENTE O APLICATIVO E A TABELA DESEJADA
— AGORA VAMOS PARA A PARTE DAS CONSTRAINTS
—
–AND FPK.PRIMARY_KEY_TYPE = ‘D’
AND FPK.TABLE_ID = FT.TABLE_ID
AND FFK.PRIMARY_KEY_APPLICATION_ID = FPK.APPLICATION_ID
AND FFK.PRIMARY_KEY_TABLE_ID = FPK.TABLE_ID
AND FFK.TABLE_ID = FT2.TABLE_ID
AND FFK.APPLICATION_ID = FT2.APPLICATION_ID
AND FPK.PRIMARY_KEY_ID = FPKC.PRIMARY_KEY_ID
AND FPK.APPLICATION_ID = FPKC.APPLICATION_ID
AND FPK.TABLE_ID = FPKC.TABLE_ID
AND FPKC.COLUMN_ID = FC.COLUMN_ID
AND FPKC.APPLICATION_ID = FC.APPLICATION_ID
AND FPKC.TABLE_ID = FC.TABLE_ID
—
AND FFK.FOREIGN_KEY_ID = FFKC.FOREIGN_KEY_ID
AND FFK.APPLICATION_ID = FFKC.APPLICATION_ID
AND FFK.TABLE_ID = FFKC.TABLE_ID
AND FFKC.COLUMN_ID = FC2.COLUMN_ID
AND FFKC.APPLICATION_ID = FC2.APPLICATION_ID
AND FFKC.TABLE_ID = FC2.TABLE_ID
ORDER BY 2,3,7,8
Autor: Rafael de Oliveira Souza