做为一名开发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
