做為一名開發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