2016年7月26日火曜日

oracle現存テーブルから再作成sql

oracleでテーブルを作り直したい時に使うsql

CREATE TABLE テーブル名 AS SELECT * FROM 既存のテーブル名;

で既存のテーブルから新規に別名のテーブルを作成する方法
(上記の場合、データも引き継がれる)

まったく空で新規につくるときに使っているsql

SELECT
 CASE DB.COLUMN_ID
     WHEN 1    THEN 'CREATE TABLE ' || DB.TABLE_NAME || '( '
     ELSE           '             '
 END
||
 TRIM(DB.COLUMN_NAME) || ' ' || TRIM(DB.DATA_TYPE) || '(' ||
 CASE DB.DATA_TYPE
       WHEN 'NUMBER' THEN TO_CHAR(DB.DATA_PRECISION,'FM000') || ',' || TO_CHAR(DB.DATA_SCALE,'FM00')
       ELSE               TO_CHAR(DB.DATA_LENGTH,'FM000')
  END
 || ')' || ' DEFAULT ' ||
 CASE DB.DATA_TYPE
       WHEN 'NUMBER' THEN '0'
       ELSE               ''' '''
  END
||
 CASE DB.NULLABLE
       WHEN 'N' THEN ' NOT NULL'
       ELSE          ' NULL'
  END    
||
CASE MAX_NO.MAX_GYO
     WHEN DB.COLUMN_ID THEN ');'
     ELSE                ','
END
 SQL_DATA
FROM user_tab_columns DB
LEFT JOIN (SELECT TABLE_NAME , MAX(COLUMN_ID) MAX_GYO FROM user_tab_columns GROUP BY TABLE_NAME) MAX_NO
ON DB.TABLE_NAME = MAX_NO.TABLE_NAME
LEFT JOIN user_col_comments DB_COL
ON DB.TABLE_NAME = DB_COL.TABLE_NAME
AND DB.COLUMN_NAME = DB_COL.COLUMN_NAME
WHERE DB.TABLE_NAME = 'もとのテーブル名'
ORDER BY  DB.TABLE_NAME,DB.COLUMN_ID
;

でテーブルcreate文が生成できるので
最初のcreateの後のテーブル名称を新しい名前にしてテーブルを作成する

意外と使う

0 件のコメント:

コメントを投稿