mysql查看表结构及已有索引信息
需求背景是给一个表名然后给出相应的表结构信息及索引信息
常用的命令有如下：
desc tableName; desc employees.employees;
www.2cto.com
show columns from tableName; show COLUMNS from employees.employees;
describe tableName; DESCRIBE employees.employees;
这三个显示的结果都是一样的，显示表中filed，type，null，key，default及extra。
show create table tableName; show CREATE TABLE employees.employees;
这个语句会显示这个表的建表语句。
select * from columns where table_name='表名';
select * from information_schema.COLUMNS where TABLE_SCHEMA='employees' and TABLE_NAME='employees';
select * from information_schema.COLUMNS where TABLE_SCHEMA='employees' and TABLE_NAME='employees';
这个显示的结果就比较全了。
接下来，来点更全的sql，这个是用来同步mysql和orac数据字典的所有sql。
mysql部分：
01
## 查看所有的库
02
SELECT
03
lower(schema_name) schema_name
04 www.2cto.com
FROM
05
information_schema.schemata
06
WHERE
07
schema_name NOT IN (
08
'mysql',
09
'information_schema',
10
'test',
11
'search',
12
'tbsearch',
13
'sbtest',
14
'dev_ddl'
15
)
16
17
## 产看某一个库中的所有表
18
SELECT
19
table_name,
20
create_time updated_at,
21
table_type,
22
ENGINE,
23
table_rows num_rows,
24 www.2cto.com
table_comment,
25
ceil(data_length / 1024 / 1024) store_capacity
26
FROM
27
information_schema.TABLES
28
WHERE
29
table_schema = 'employees'
30
AND table_name NOT LIKE 'tmp#_%' ESCAPE '#'
31
32
##查看某一个库下某一个表的所有字段
33
SELECT
34
lower(column_name) column_name,
35
ordinal_position position,
36
column_default dafault_value,
37
substring(is_nullable, 1, 1) nullable,
38
column_type data_type,
39
column_comment,
40
character_maximum_length data_length,
41
numeric_precision data_precision,
42
numeric_scale data_scale
43
FROM
44
information_schema.COLUMNS
45 www.2cto.com
WHERE
46
table_schema = 'employees'
47
AND table_name = 'employees';
48
49
50
## 查看某一个库下某一张表的索引
51
52
SELECT DISTINCT
53
lower(index_name) index_name,
54
lower(index_type) type
55
FROM
56
information_schema.statistics
57
WHERE
58
table_schema = 'employees'
59
AND table_name = 'employees';
60
61
## 查看某一个库下某一张表的某一个索引
62
63
SELECT
64
lower(column_name) column_name,
65
seq_in_index column_position
66
FROM
67
information_schema.statistics
68
WHERE
69
table_schema = 'employees'
70
AND table_name = 'employees'
71
AND index_name = 'primary';
72
www.2cto.com
73
## 查看某一个库下某一个表的注释
74
SELECT
75
table_comment comments
76
FROM
77
information_schema.TABLES
78
WHERE
79
table_schema = 'employees'
80
AND table_name = 'employees';
81
82
## 查看某一个库下某一个表的列的注释
83
SELECT
84
lower(column_name) column_name,
85
column_comment comments
86
FROM
87
COLUMNS
88
WHERE
89
table_schema = 'employees'
90
AND table_name = 'employees';
oracle部分：
www.2cto.com
001
#table structure:
002
SELECT
003
lower(tabl