熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> SQL語言 >> 正文

檢查數據庫數據字段命名規范與合法性的腳本

2022-06-13   來源: SQL語言 

  做為一名開發DBA最重要的renweu是經常要檢查開發人員或建模人員對數據庫的命名規范是否嚴謹下文中將主要介紹一個檢查數據庫數據字段命名規范與合法性的腳本僅供大家參考

  檢查不符合數據庫命名規范的字段名

  Select TABLE_NAMECOLUMN_NAMEDATA_TYPE
  From user_tab_columns
  Where column_name In (Select keyword From v$reserved_words)
  (ACCESSADDALLALTERANDANYASASCAUDIT
  BETWEENBYCHARCHECKCLUSTER
  COLUMNCOMMENTCOMPRESSCONNECTCREATECURRENT
  DATEDECIMALDEFAULTDELETE
  DESCDISTINCTDROPELSEEXCLUSIVEEXISTSFILE
  FLOATFORFROMGRANTGROUP
  HAVINGIDENTIFIEDIMMEDIATEININCREMENTINDEX
  INITIALINSERTINTEGERINTERSECT
  INTOISLEVELLIKELOCKLONGMAXEXTENTSMINUS
  MLSLABELMODEMODIFYNOAUDIT
  NOCOMPRESSNOTNOWAITNULLNUMBEROFOFFLINEON
  ONLINEOPTIONORORDER
  PCTFREEPRIORPRIVILEGESPUBLICRAWRENAMERESOURCE
  REVOKEROWROWIDROWNUM
  ROWSSELECTSESSIONSETSHARESIZESMALLINTSTART
  SUCCESSFULSYNONYMSYSDATE
  TABLETHENTOTRIGGERUIDUNIONUNIQUEUPDATE
  USERVALIDATEvalueSVARCHAR
  VARCHARVIEWWHENEVERWHEREWITH)
  Or column_name Like % %

  檢查數據庫中相同名字不同類型的字段名

  select acolumn_nameadata_typebdata_type From
  (select distinct column_namedata_type from
  all_tab_columns Where TABLE_NAME Like T%) a
  (select distinct column_namedata_type from
  all_tab_columns Where TABLE_NAME Like T%) b
  where acolumn_name=bcolumn_name and adata_type<>bdata_type

  檢查數據庫中相同名字相同類型不同長度的字段名

  select acolumn_nameadata_typebdata_type
  adata_lengthbdata_length From
  (select distinct column_namedata_type
  data_length from all_tab_columns Where TABLE_NAME Like T%) a
  (select distinct column_namedata_type
  data_length from all_tab_columns Where TABLE_NAME Like T%) b
  where acolumn_name=bcolumn_name and adata_type=bdata_type And ADATA_LENGTH<>BDATA_LENGTH


From:http://tw.wingwit.com/Article/program/SQL/201311/16267.html
    Copyright © 2005-2022 電腦知識網 Computer Knowledge   All rights reserved.