-- Generate table layouts
SELECT (CASE WHEN COLUMN_ID = 1 THEN 'CREATE TABLE ' || TABLE_NAME || '('
ELSE ',' END) || COLUMN_NAME || ' ' ||
DATA_TYPE ||
(CASE WHEN DATA_TYPE LIKE '%CHAR%' THEN '(' || RTRIM(TO_CHAR(CHAR_LENGTH)) || ')'
WHEN DATA_TYPE = 'NUMBER' AND DATA_PRECISION IS NOT NULL THEN
'(' || RTRIM(TO_CHAR(DATA_PRECISION)) || ',' || DATA_SCALE || ')'
END) ||
(CASE WHEN NULLABLE = 'Y' THEN '' ELSE ' NOT NULL ' END) ||
(CASE WHEN COLUMN_ID = MAX_COLNO THEN ');' ELSE '' END)
FROM ALL_TAB_COLUMNS,
(SELECT TABLE_NAME TBNAME,MAX(COLUMN_ID) MAX_COLNO
FROM ALL_TAB_COLUMNS
WHERE OWNER = 'ORION'
GROUP BY TABLE_NAME) T,
(SELECT TABLE_NAME UTABLE
FROM ALL_TABLES
WHERE OWNER = 'ORION'
AND DROPPED = 'NO'
AND TABLE_NAME NOT IN (SELECT TABLE_NAME FROM ALL_SNAPSHOTS)
AND TABLE_NAME NOT IN (SELECT TABLE_NAME FROM ALL_EXTERNAL_TABLES)) T1
WHERE OWNER = 'ORION'
AND TABLE_NAME = TBNAME
AND TABLE_NAME = UTABLE
ORDER BY TABLE_NAME,COLUMN_ID;
-- GENERATE ALTER PRIMARY KEY
SELECT (CASE WHEN POSITION = MINPO THEN
'ALTER TABLE ' || RTRIM(T0.TABLE_NAME) || ' ADD CONSTRAINT ' || T0.CONSTRAINT_NAME ||
' PRIMARY KEY ('
ELSE ',' END) || COLUMN_NAME ||
(CASE WHEN POSITION = MAXPO THEN ');' ELSE '' END)
FROM ALL_CONS_COLUMNS,
(SELECT CONSTRAINT_NAME,TABLE_NAME
FROM ALL_CONSTRAINTS
WHERE OWNER = 'ORION'
AND CONSTRAINT_TYPE = 'P'
) T0,
(SELECT CONSTRAINT_NAME,MIN(POSITION) MINPO,MAX(POSITION) MAXPO
FROM ALL_CONS_COLUMNS
WHERE OWNER = 'ORION'
AND POSITION IS NOT NULL
GROUP BY CONSTRAINT_NAME
) T1
WHERE OWNER = 'ORION'
AND ALL_CONS_COLUMNS.CONSTRAINT_NAME = T0.CONSTRAINT_NAME
AND T0.CONSTRAINT_NAME = T1.CONSTRAINT_NAME
AND ALL_CONS_COLUMNS.TABLE_NAME NOT IN (SELECT TABLE_NAME FROM ALL_TABLES WHERE DROPPED <> 'NO');
-- GENERATE FOREIGN KEY
SELECT STRSQL
FROM
(
SELECT (CASE WHEN ALL_CONS_COLUMNS.POSITION = R1.MINPO THEN
'ALTER TABLE ' || RTRIM(R0.TABLE_NAME) || ' ADD CONSTRAINT ' || R0.CONSTRAINT_NAME ||
' FOREIGN KEY ('
ELSE ',' END) || ALL_CONS_COLUMNS.COLUMN_NAME ||
(CASE WHEN ALL_CONS_COLUMNS.POSITION = R1.MAXPO THEN ')' ELSE '' END) STRSQL,
ALL_CONS_COLUMNS.TABLE_NAME,R_CONSTRAINT_NAME,0 OID
FROM ALL_CONS_COLUMNS,
(SELECT CONSTRAINT_NAME,TABLE_NAME,R_CONSTRAINT_NAME
FROM ALL_CONSTRAINTS
WHERE OWNER = 'ORION'
AND CONSTRAINT_TYPE = 'R') R0,
(SELECT CONSTRAINT_NAME,MIN(POSITION) MINPO,MAX(POSITION) MAXPO
FROM ALL_CONS_COLUMNS
WHERE OWNER = 'ORION'
AND POSITION IS NOT NULL
GROUP BY CONSTRAINT_NAME) R1
WHERE ALL_CONS_COLUMNS.OWNER = 'ORION'
AND ALL_CONS_COLUMNS.CONSTRAINT_NAME = R0.CONSTRAINT_NAME
AND R0.CONSTRAINT_NAME = R1.CONSTRAINT_NAME
AND ALL_CONS_COLUMNS.TABLE_NAME NOT IN (SELECT TABLE_NAME FROM ALL_TABLES WHERE DROPPED <> 'NO')
UNION ALL
SELECT (CASE WHEN ALL_CONS_COLUMNS.POSITION = P1.MINPO THEN
' REFERENCES ' || RTRIM(P1.PNAME) || ' ('
ELSE ',' END) || ALL_CONS_COLUMNS.COLUMN_NAME ||
(CASE WHEN ALL_CONS_COLUMNS.POSITION = P1.MAXPO THEN ');' ELSE '' END) STRSQL,
ALL_CONS_COLUMNS.TABLE_NAME,P1.CONSTRAINT_NAME,1 OID
FROM (SELECT CONSTRAINT_NAME,TABLE_NAME,R_CONSTRAINT_NAME
FROM ALL_CONSTRAINTS
WHERE OWNER = 'ORION'
AND CONSTRAINT_TYPE = 'R') R0,
(SELECT CONSTRAINT_NAME,TABLE_NAME PNAME,MIN(POSITION) MINPO,MAX(POSITION) MAXPO
FROM ALL_CONS_COLUMNS
WHERE OWNER = 'ORION'
AND POSITION IS NOT NULL
GROUP BY CONSTRAINT_NAME,TABLE_NAME) P1,
ALL_CONS_COLUMNS
WHERE R0.R_CONSTRAINT_NAME = P1.CONSTRAINT_NAME
AND ALL_CONS_COLUMNS.OWNER = 'ORION'
AND ALL_CONS_COLUMNS.CONSTRAINT_NAME = R0.R_CONSTRAINT_NAME
AND ALL_CONS_COLUMNS.TABLE_NAME NOT IN (SELECT TABLE_NAME FROM ALL_TABLES WHERE DROPPED <> 'NO')
) TMP
ORDER BY R_CONSTRAINT_NAME,OID;
-- EASIEST WAY TO GENERATE DOCUMENT OF CURRENT TABLES LAYOUTS
SELECT 'TableName: ' || TABLE_NAME TNAME,'Description: ' || COALESCE(COMMENTS,'') TDESC
FROM ALL_TAB_COMMENTS
WHERE OWNER = 'ORION'
AND TABLE_TYPE = 'TABLE'
AND TABLE_NAME NOT IN (SELECT TABLE_NAME FROM ALL_TABLES WHERE DROPPED <> 'NO')
AND TABLE_NAME NOT IN (SELECT TABLE_NAME FROM ALL_SNAPSHOTS)
AND TABLE_NAME NOT IN (SELECT TABLE_NAME FROM ALL_EXTERNAL_TABLES)
ORDER BY TABLE_NAME;
SELECT * FROM ALL_COL_COMMENTS WHERE OWNER = 'ORION'
SELECT TMP.TABLE_NAME,TMP.COLUMN_NAME,COMMENTS,DATA_TYPE,NULLABLE,
COALESCE(PCTYPE,'N') PK,COALESCE(RCTYPE,'N') FK
FROM
(
SELECT TABLE_NAME,COLUMN_NAME,
DATA_TYPE ||
(CASE WHEN DATA_TYPE LIKE '%CHAR%' THEN '(' || RTRIM(TO_CHAR(CHAR_LENGTH)) || ')'
WHEN DATA_TYPE = 'NUMBER' AND DATA_PRECISION IS NOT NULL THEN
'(' || RTRIM(TO_CHAR(DATA_PRECISION)) || ',' || DATA_SCALE || ')'
END) DATA_TYPE,NULLABLE,COLUMN_ID
FROM ALL_TAB_COLUMNS,
(SELECT TABLE_NAME UTABLE
FROM ALL_TABLES
WHERE OWNER = 'ORION'
AND DROPPED = 'NO'
AND TABLE_NAME NOT IN (SELECT TABLE_NAME FROM ALL_SNAPSHOTS)
AND TABLE_NAME NOT IN (SELECT TABLE_NAME FROM ALL_EXTERNAL_TABLES)) T1
WHERE OWNER = 'ORION'
AND TABLE_NAME = UTABLE
) TMP
LEFT JOIN ALL_COL_COMMENTS ON OWNER = 'ORION'
AND ALL_COL_COMMENTS.TABLE_NAME = TMP.TABLE_NAME
AND ALL_COL_COMMENTS.COLUMN_NAME = TMP.COLUMN_NAME
LEFT JOIN (SELECT ALL_CONSTRAINTS.TABLE_NAME PNAME,'Y' PCTYPE,
COLUMN_NAME PCNAME
FROM ALL_CONSTRAINTS,ALL_CONS_COLUMNS
WHERE ALL_CONSTRAINTS.CONSTRAINT_NAME = ALL_CONS_COLUMNS.CONSTRAINT_NAME
AND ALL_CONSTRAINTS.OWNER = 'ORION'
AND ALL_CONSTRAINTS.CONSTRAINT_TYPE = 'P') P ON P.PNAME = TMP.TABLE_NAME
AND P.PCNAME = TMP.COLUMN_NAME
LEFT JOIN (SELECT ALL_CONSTRAINTS.TABLE_NAME RNAME,'Y' RCTYPE,
COLUMN_NAME RCNAME
FROM ALL_CONSTRAINTS,ALL_CONS_COLUMNS
WHERE ALL_CONSTRAINTS.CONSTRAINT_NAME = ALL_CONS_COLUMNS.CONSTRAINT_NAME
AND ALL_CONSTRAINTS.OWNER = 'ORION'
AND ALL_CONSTRAINTS.CONSTRAINT_TYPE = 'R') R ON R.RNAME = TMP.TABLE_NAME
AND R.RCNAME = TMP.COLUMN_NAME
ORDER BY TMP.TABLE_NAME,TMP.COLUMN_ID;
