資料庫欄位命名規範與合法性檢查腳本：保留字衝突、同名異型、同名異長度三合一查詢

三大命名風險檢查

1. 違反保留字命名規範

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM user_tab_columns
WHERE column_name IN (
    'ACCESS','ADD','ALL','ALTER','AND','ANY','AS','ASC','AUDIT',
    'BETWEEN','BY','CHAR','CHECK','CLUSTER','COLUMN','COMMENT',
    'COMPRESS','CONNECT','CREATE','CURRENT','DATE','DECIMAL',
    'DEFAULT','DELETE','DESC','DISTINCT','DROP','ELSE','EXCLUSIVE',
    'EXISTS','FILE','FLOAT','FOR','FROM','GRANT','GROUP','HAVING',
    'IDENTIFIED','IMMEDIATE','IN','INCREMENT','INDEX','INITIAL',
    'INSERT','INTEGER','INTERSECT','INTO','IS','LEVEL','LIKE',
    'LOCK','LONG','MAXEXTENTS','MINUS','MLSLABEL','MODE','MODIFY',
    'NOAUDIT','NOCOMPRESS','NOT','NOWAIT','NULL','NUMBER','OF',
    'OFFLINE','ON','ONLINE','OPTION','OR','ORDER','PCTFREE','PRIOR',
    'PRIVILEGES','PUBLIC','RAW','RENAME','RESOURCE','REVOKE','ROW',
    'ROWID','ROWNUM','ROWS','SELECT','SESSION','SET','SHARE','SIZE',
    'SMALLINT','START','SUCCESSFUL','SYNONYM','SYSDATE','TABLE',
    'THEN','TO','TRIGGER','UID','UNION','UNIQUE','UPDATE','USER',
    'VALIDATE','VALUES','VARCHAR','VARCHAR2','VIEW','WHENEVER',
    'WHERE','WITH')
   OR column_name LIKE '% %';  -- 含空白字元

2. 同名異型別（跨表欄位型別不一致）

SELECT a.column_name, a.data_type, b.data_type
FROM
    (SELECT DISTINCT column_name, data_type FROM all_tab_columns WHERE TABLE_NAME LIKE 'T%') a,
    (SELECT DISTINCT column_name, data_type FROM all_tab_columns WHERE TABLE_NAME LIKE 'T%') b
WHERE a.column_name = b.column_name
  AND a.data_type <> b.data_type;

3. 同名同型別異長度（VARCHAR2(20) vs VARCHAR2(50)）

SELECT a.column_name, a.data_type, b.data_type,
       a.data_length, b.data_length
FROM
    (SELECT DISTINCT column_name, data_type, data_length FROM all_tab_columns WHERE TABLE_NAME LIKE 'T%') a,
    (SELECT DISTINCT column_name, data_type, data_length FROM all_tab_columns WHERE TABLE_NAME LIKE 'T%') b
WHERE a.column_name = b.column_name
  AND a.data_type = b.data_type
  AND a.data_length <> b.data_length;

應用建議

1. 開發初期建立命名標準文件（前綴、大小寫、縮寫規則）。
2. 部署前跑此三支腳本，輸出清單交由架構師審核。
3. 納入 CI/CD 流程自動化檢查，阻擋不合規結構上線。
4. Oracle 環境可改查 V$RESERVED_WORDS 動態取得保留字清單。