desc tableName; desc employees.employees;
www.2cto.com
show columns from tableName; show COLUMNS from employees.employees;
describe tableName; DESCRIBE employees.employees;

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

select * from information_schema.COLUMNS where TABLE_SCHEMA='employees' and TABLE_NAME='employees';

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';

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 = ?;

http://book.51cto.com/art/201108/288137.htm

--转自