Posted on 2020-01-15 17:04
魔のkyo 阅读(211)
评论(0) 编辑 收藏 引用 所属分类:
SQL
SQL Server(version 2005)读取数据库中的表信息
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)读取表中的列信息
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)读取数据库中的表信息
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)读取表中的列信息
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 |
价格 |