基于模板的通用代码生成器LKGenerator(四)-核心技术之各种数据库查询表信息sql整理

本文主要整理出了代码生成器中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,否则可能查不到任何结果)

3.查询主键:

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'

限于本人水平有限,很多地方写的并不完美,希望大家不吝赐教.不足之处欢迎留言交流,希望在和大家的交流中得到提高.