本文主要整理出了代码生成器中Mysql,Orclece,SqlSever分别使用的查询数据库表信息的sql,主要包括:1.查询数据库中的所有表. 2.查询数据表列名,是否可为空,最大长度,备注信息. 3.主键.
以下以数据库school为例,表以t_student为例.
(一) MySql:
1.查询数据库school中的所有表的表名
select table_name from information_schema.tables t where table_schema = ‘school‘
2.查出列名,列数据类型,是否可为空,最大长度,备注
select column_name, data_type, t.is_nullable, t.character_maximum_length length, t.column_comment memo from information_schema.columns t where table_name = ‘t_student‘ and table_schema = ‘school‘ --注意:查询条件中需加入table_schema = ‘school‘否则如果多个数据库中都存在t_student表时,将查出多余的列信息.
3.查询主键
select column_name from information_schema.columns t Where table_name = ‘t_student‘ and column_key = ‘PRI‘ and table_schema = ‘school‘
(二) Oracle:
1.查询数据库school中的所有表的表名
select * from sys.all_tables where owner = ‘SCHOOL‘ --(注意需大写SCHOOL,否则可能查不到任何结果)
2.查出列名,列数据类型,是否可为空,最大长度,备注
select utcom.column_name, utcom.data_type, utcom.data_length length, data_precision, data_scale, utcom.NULLABLE, ucc.comments memo from sys.user_tab_columns utcom join USER_COL_COMMENTS ucc on (utcom.TABLE_NAME = ucc.table_name and utcom.COLUMN_NAME = ucc.column_name) Where utcom.table_name = ‘T_STUDENT‘ --(注意需大写T_STUDENT,否则可能查不到任何结果)
select c.index_name, c.column_name, data_type, data_precision, data_scale from Sys.user_constraints i, Sys.all_ind_columns c, sys.user_tab_columns u Where i.TABLE_NAME = ‘T_STUDENT‘ And i.CONSTRAINT_TYPE = ‘P‘ And i.CONSTRAINT_name = c.index_name And c.column_name = u.column_name And u.table_name = ‘T_STUDENT‘ Order By index_name --(注意需大写T_STUDENT,否则可能查不到任何结果)
(三)SqlServer:
1.查询数据库school中的所有表的表名
select name table_name from dbo.sysobjects where xtype=‘u‘
2.查出列名,列数据类型,是否可为空,最大长度,备注
SELECT col.name, typ.name as data_type, col.max_length length, col.is_nullable, ep.value memo FROM sys.columns col left join sys.types typ on (col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id) left join sys.extended_properties ep on (col.object_id = ep.major_id and col.column_id = ep.minor_id) WHERE col.object_id = (SELECT object_id FROM sys.tables WHERE name = ‘t_student‘)
3.查找主键
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = ‘t_student‘
限于本人水平有限,很多地方写的并不完美,希望大家不吝赐教.不足之处欢迎留言交流,希望在和大家的交流中得到提高.
基于模板的通用代码生成器LKGenerator(四)-核心技术之各种数据库查询表信息sql整理,布布扣,bubuko.com