Oracle创建语句

# 查看当前用户下所有的主键，并拼接成创建语句
SQL> SELECT
       'ALTER TABLE ' || A.TABLE_NAME || ' ADD CONSTRAINT ' || A.CONSTRAINT_NAME || ' PRIMARY KEY (' || A.COLUMN_NAME || ');'
      FROM
        USER_CONS_COLUMNS A
      JOIN USER_CONSTRAINTS B
          ON
        A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
        AND A.POSITION = 1
        AND B.CONSTRAINT_TYPE = 'P';
        
# 查看当前用户下所有的外键，并拼接成创建语句
SQL> SELECT DISTINCT 'ALTER TABLE ' || CON1.QCSJ_C000000000400004 ||
                    ' ADD CONSTRAINT ' || CON1.QCSJ_C000000000400002 ||
                    ' FOREIGN KEY (' || CON1.COLUMN_NAME || ') REFERENCES ' ||
                    CON2.TABLE_NAME || ' (' || CON2.COLUMN_NAME || ');'
      FROM (SELECT *
              FROM USER_CONS_COLUMNS A
              JOIN USER_CONSTRAINTS B
                ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
               AND A.POSITION = 1
               AND B.CONSTRAINT_TYPE = 'R') CON1,
           (SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME
              FROM (SELECT A1.CONSTRAINT_NAME,
                           A1.TABLE_NAME,
                           A1.COLUMN_NAME,
                           B1.CONSTRAINT_TYPE,
                           B1.R_CONSTRAINT_NAME
                      FROM USER_CONS_COLUMNS A1
                      JOIN USER_CONSTRAINTS B1
                        ON A1.CONSTRAINT_NAME = B1.CONSTRAINT_NAME
                     WHERE A1.POSITION = 1
                       and a1.CONSTRAINT_NAME in
                           ((SELECT B1.R_CONSTRAINT_NAME
                              FROM USER_CONS_COLUMNS A1
                              JOIN USER_CONSTRAINTS B1
                                ON A1.CONSTRAINT_NAME = B1.CONSTRAINT_NAME
                             WHERE A1.POSITION = 1)))) CON2
     WHERE CON1.R_CONSTRAINT_NAME = CON2.CONSTRAINT_NAME;

# 查看当前用户下所有的序列，并拼接成创建语句
SQL> SELECT 'CREATE SEQUENCE ' || SEQUENCE_NAME || ' MINVALUE ' || MIN_VALUE ||
           ' MAXVALUE ' || MAX_VALUE || ' START WITH ' || LAST_NUMBER ||
           ' INCREMENT BY ' || INCREMENT_BY ||(CASE
             WHEN CACHE_SIZE = 0 THEN
              ' NOCACHE'
             ELSE
              ' CACHE ' ||CACHE_SIZE
           END)||';'
      FROM USER_SEQUENCES;  
      
# 查看没有创建主键的表
SQL> SELECT U.TABLE_NAME, U.NUM_ROWS FROM USER_TABLES U
      WHERE NOT EXISTS (SELECT CU.TABLE_NAME
                FROM USER_CONS_COLUMNS CU, USER_CONSTRAINTS AU
               WHERE CU.CONSTRAINT_NAME = AU.CONSTRAINT_NAME
                 AND AU.CONSTRAINT_TYPE = 'P'
                 AND AU.TABLE_NAME = U.TABLE_NAME)
         AND U.NUM_ROWS IS NOT NULL
      ORDER BY U.NUM_ROWS DESC;