| 订阅 | 在线投稿
分享
 
 
 

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

来源:互联网网民  宽屏版  评论
2008-06-07 09:56:27

做为一名开发DBA,最重要的renweu是经常要检查开发人员或建模人员对数据库的命名规范是否严谨,下文中将主要介绍一个检查数据库数据字段命名规范与合法性的脚本,仅供大家参考。(关键字详细信息可以查看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

 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
做为一名开发DBA,最重要的renweu是经常要检查开发人员或建模人员对数据库的命名规范是否严谨,下文中将主要介绍一个检查数据库数据字段命名规范与合法性的脚本,仅供大家参考。(关键字详细信息可以查看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
󰈣󰈤
 
 
 
>>返回首页<<
 
 热帖排行
 
 
静静地坐在废墟上,四周的荒凉一望无际,忽然觉得,凄凉也很美
©2005- 王朝网络 版权所有