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';
這個顯示的結果就比較全了。
接下來，來點更全的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(table_name) table_name,
004
TEMPORARY,
005
tablespace_name,
006
num_rows,
007
duration,
008
'ORACLE' table_type,
009 www.2cto.com
partitioned,
010
(
011
SELECT
012
ceil(sum(bytes) / 1024 / 1024)
013
FROM
014
dba_segments b
015
WHERE
016
a. OWNER = b. OWNER
017
AND a.table_name = b.segment_name
018
) AS store_capacity
019
FROM
020
dba_tables a
021
WHERE
022
OWNER = ?
023
AND table_name NOT LIKE 'TMP%';
024
025
SELECT
026
lower(column_name) column_name,
027
column_id position,
028
data_type,
029
data_length,
030
data_precision,
031
data_scale,
032
nullable,
033
data_default default_value,
034 www.2cto.com
default_length
035
FROM
036
dba_tab_columns
037
WHERE
038
OWNER = ?
039
AND table_name = ?;
040
041
# index
042
SELECT
043
lower(index_name) index_name,
044
index_type type
045
FROM
046
dba_indexes
047
WHERE
048
OWNER = ?
049
AND table_name = ?
050
AND index_name NOT LIKE 'SYS_IL%';
051
052
SELECT
053
lower(column_name) column_name,
054
column_position,
055
descend
056
FROM
057
dba_ind_columns
058
WHERE
059
table_owner = ?
060
AND table_name = ?
061
AND index_name = ?;
062
www.2cto.com
063
#collect description
064
SELECT
065
comments
066
FROM
067
dba_tab_comments
068
WHERE
069
OWNER = ?
070
AND table_name = ?;
071
072
SELECT
073
lower(column_name) column_name,
074
comments
075
FROM
076
dba_col_comments
077
WHERE
078
OWNER = ?
079
AND table_name = ?;
080
081
#database
082
SELECT
083
lower(username) username
084
FROM
085
dba_users
086
WHERE
087
username NOT IN (
088
'STDBYPERF',
089
'READONLY',
090
'APPQOSSYS',
091
'ANYSQL',
092
'DBFLASH',
093
'SYS',
094
'SYSTEM',
095
'MONITOR',
096
'TBSEARCH',
097
'MANAGER',
098 www.2cto.com
'SYSMAN',
099
'EXFSYS',
100
'WMSYS',
101
'DIP',
102
'TSMSYS',
103
'ORACLE_OCM',
104
'OUTLN',
105
'DBSNMP',
106
'PERFSTAT',
107
'SEARCH',
108
'TOOLS',
109
'TBDUMP',
110
'DMSYS',
111
'XDB',
112
'ANONYMOUS',
113
'DEV_DDL'
114
);
115
116
#segsize
117
SELECT
118
round(sum(bytes) / 1024 / 1024, 0) mbytes
119
FROM
120 www.2cto.com
dba_segments
121
WHERE
122
OWNER = ?
123
AND segment_name = ?;
關於oralce中的segements，可以參考一下這個系列文章。
http://book.51cto.com/art/201108/288137.htm
總結一下，mysql中查看庫表字段信息都在information_schemal中，這些是獲取數據字典的必備sql。
本文中mysql的語句都在本地測試過。另外oracle的結構也要熟悉。
作者 艮子明
