檢查資料庫資料欄位命名規範和合法性的指令碼

做開發DBA重要的是經常要檢查開發人員或建模人員對資料庫的命名規範是否嚴謹。。。

關鍵字詳細資訊可以檢視v$reserved_words檢視

--檢查不符合資料庫命名規範的欄位名
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