mysql查看表结构及已有索引信息(二)
e_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的结构也要熟悉。
本文中mysql的语句都在本地测试过。另外oracle的结构也要熟悉。
作者 艮子明