mysql查看表结构信息

原文地址： http://my.oschina.net/zimingforever/blog/64145

需求背景是给一个表名然后给出相应的表结构信息及索引信息

常用的命令有如下：

1. desc tableName; desc employees.employees;

2. show columns from tableName; show COLUMNS from employees.employees;

3. describe tableName; DESCRIBE employees.employees;

这三个显示的结果都是一样的，显示表中filed，type，null，key，default及extra。

4. show create table tableName; show CREATE TABLE employees.employees;

这个语句会显示这个表的建表语句。

5. select * from columns where table_name='表名';select * from information_schema.COLUMNS where TABLE_SCHEMA='employees' and TABLE_NAME='employees';

这个显示的结果就比较全了。

接下来，来点更全的sql，这个是用来同步mysql和orac数据字典的所有sql。

mysql部分：

 1 ## 查看所有的库
 2 SELECT
 3     lower(schema_name) schema_name
 4 FROM
 5     information_schema.schemata
 6 WHERE
 7     schema_name NOT IN (
 8         'mysql',
 9         '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     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 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  
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';

 

posted @ 2015-12-30 19:06  桦沐  阅读(1484)  评论(0)  编辑  收藏  举报