Scripts that check the naming rules and validity of database data fields

-- Check the field names that do not comply with the database naming rules

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', 'companys', 'connect', 'create', 'current ',

'Date', 'decimal', 'default', 'delete ',

'Desc', 'did', 'drop', 'else', 'clusive ', 'exists', 'file ',

'Float', 'for', 'from', 'Grant ', 'group ',

'Having', 'identified', 'immediate', 'in', 'credentials', 'index ',

'Initial', 'insert', 'integer', 'intersect ',

'Input', 'is ', 'level', 'like', 'lock', 'long', 'maxextents', 'minus ',

'Mlslabel ', 'Mode', 'modify', 'noaudit ',

'Nocomputing', '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', 'unique', 'update ',

'User', 'validate', 'values', 'varchar ',

'Varchar2', 'view', 'whenever', 'where', 'with ')

Or column_name like '%'

 

-- Check the field names of different types with the same name in the database

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 % '),

(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

 

-- Check the field names with the same name, the same type, and different lengths in the database.

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 % '),

(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