mysql查看哪些表有索引_mysql查看表结构及已有索引信息

查看表结构及已有索引信息需求背景是给一个表名然后给出相应的表结构信息及索引信息常用的命令有如下：desc tableName; desc employees.employees; www.2cto.com show columns from tableName; show COLUMNS from employees.employees;describe tableName; DE

查看表结构及已有索引信息

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

常用的命令有如下：

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的结构也要熟悉。

作者 艮子明