数据库元数据查询

发表于 更新于 分类于 Valine： 本文字数： 2.6k 阅读时长 ≈ 2 分钟

SQL Server(version 2005)读取数据库中的表信息

1
2
3
4
5
6
select
  name as 'table_name',
  create_date AS 'create_time',
  modify_date AS 'update_time'
from sys.tables
where type = 'U'

SQL Server(version 2005)读取表中的列信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT
  a.name as 'column_name',
    b.name as 'data_type',
    COLUMNPROPERTY(a.id,a.name,'PRECISION') as 'data_length',
    COLUMNPROPERTY(a.id,a.name,'Scale') as 'data_scale',
    case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
      SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then 1 else 0 end as 'primary_key',
    COLUMNPROPERTY(a.id,a.name,'IsIdentity') as 'autoincrement',
    a.isnullable as 'nullable',
    e.text as 'column_default',
    g.value as 'column_comment'
FROM syscolumns a
left join systypes b on a.xusertype=b.xusertype
left join sysobjects d on a.id=d.id and d.xtype='U'
left join syscomments e on a.cdefault=e.id
left join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id
where d.name='Role'
order by a.id, a.colorder

查询结果示例如下：

column_name data_type data_length data_scale primary_key autoincrement nullable column_default column_comment
id int 10 0 1 1 0
name nvarchar 50 0 0 0 名称
description nvarchar 300 0 0 1 描述

MySQL(version 5.7)读取数据库中的表信息

1
2
3
4
5
6
7
8
SELECT
  TABLE_NAME AS 'table_name',
  TABLE_ROWS AS 'table_rows',
  CREATE_TIME AS 'create_time',
  UPDATE_TIME AS 'update_time'
FROM
  information_schema.TABLES
WHERE TABLE_SCHEMA ='test'

其中TABLE_SCHEMA是数据库名。
查询结果示例如下：

table_name table_rows create_time update_time
goods 5 2020/1/15 17:10 2020/1/15 17:10

其中update_time是表结构的更新时间，而不是表数据的更新时间，而且我测试下来create_time也会跟着变化不知为何。

MySQL(version 5.7)读取表中的列信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
  COLUMN_NAME as 'column_name',
  DATA_TYPE as 'data_type',
  IFNULL(CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION) as 'data_length',
  NUMERIC_SCALE as 'data_scale',
  COLUMN_TYPE as 'column_type',
  IF(COLUMN_KEY='PRI', 1, 0) as 'primary_key',
  IF(EXTRA='auto_increment', 1, 0) as 'autoincrement',
  IF(IS_NULLABLE='YES', 1, 0) as 'nullable',
  COLUMN_DEFAULT as 'column_default',
  COLUMN_COMMENT as 'column_comment'
FROM
  information_schema.COLUMNS
WHERE TABLE_SCHEMA ='test' and TABLE_NAME = 'goods'

其中TABLE_SCHEMA是数据库名，TABLE_NAME是表名。
查询结果示例如下：

column_name data_type data_length data_scale column_type primary_key autoincrement nullable column_default column_comment
id int 10 0 int(11) 1 1 0
name varchar 100 varchar(100) 0 0 1 名称
price decimal 18 2 decimal(18,2) 0 0 0 0 价格