检查数据库数据字段命名规范与合法性的脚本

检查数据库数据字段命名规范与合法性的脚本2010-09-17Burgess--检查不符合数据库命名规范的字段名

Select TABLE_NAME,COLUMN_NAME,DATA_TYPE
From user_tab_columns
Where column_name In --（Select keyword From v$reserved_words）
（"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 "% %"

--检查数据库中相同名字不同类型的字段名

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

--检查数据库中相同名字相同类型不同长度的字段名

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