原文地址:http://my.oschina.net/zimingforever/blog/64145
1、查看表结构
desc tableName;
show columns from tableName;
describe tableName;
这三个显示的结果都是一样的,显示表中filed,type,null,key,default及extra。
--TABLE_SCHEMA表示数据库名称
select * from information_schema.COLUMNS where TABLE_SCHEMA='gm' and TABLE_NAME='t_role';
这个显示的结果就比较全了。
2、查看建表语句
show create table tableName;
3、接下来,来点更全的sql,这个是用来同步mysql和orac数据字典的所有sql。
mysql部分:
## 查看所有的库
SELECT
lower(schema_name) schema_name
FROM
information_schema.schemata
WHERE
schema_name NOT IN (
'mysql',
'information_schema',
'test',
'search',
'tbsearch',
'sbtest',
'dev_ddl'
)
## 产看某一个库中的所有表
SELECT
table_name,
create_time updated_at,
table_type,
ENGINE,
table_rows num_rows,
table_comment,
ceil(data_length / 1024 / 1024) store_capacity
FROM
information_schema.TABLES
WHERE
table_schema = 'employees'
AND table_name NOT LIKE 'tmp#_%' ESCAPE '#'
##查看某一个库下某一个表的所有字段
SELECT
lower(column_name) column_name,
ordinal_position position,
column_default dafault_value,
substring(is_nullable, 1, 1) nullable,
column_type data_type,
column_comment,
character_maximum_length data_length,
numeric_precision data_precision,
numeric_scale data_scale
FROM
information_schema.COLUMNS
WHERE
table_schema = 'employees'
AND table_name = 'employees';
## 查看某一个库下某一张表的索引
SELECT DISTINCT
lower(index_name) index_name,
lower(index_type) type
FROM
information_schema.statistics
WHERE
table_schema = 'employees'
AND table_name = 'employees';
## 查看某一个库下某一张表的某一个索引
SELECT
lower(column_name) column_name,
seq_in_index column_position
FROM
information_schema.statistics
WHERE
table_schema = 'employees'
AND table_name = 'employees'
AND index_name = 'primary';
## 查看某一个库下某一个表的注释
SELECT
table_comment comments
FROM
information_schema.TABLES
WHERE
table_schema = 'employees'
AND table_name = 'employees';
## 查看某一个库下某一个表的列的注释
SELECT
lower(column_name) column_name,
column_comment comments
FROM
COLUMNS
WHERE
table_schema = 'employees'
AND table_name = 'employees';
oracle部分:
#table structure:
SELECT
lower(table_name) table_name,
TEMPORARY,
tablespace_name,
num_rows,
duration,
'ORACLE' table_type,
partitioned,
(
SELECT
ceil(sum(bytes) / 1024 / 1024)
FROM
dba_segments b
WHERE
a. OWNER = b. OWNER
AND a.table_name = b.segment_name
) AS store_capacity
FROM
dba_tables a
WHERE
OWNER = ?
AND table_name NOT LIKE 'TMP%';
SELECT
lower(column_name) column_name,
column_id position,
data_type,
data_length,
data_precision,
data_scale,
nullable,
data_default default_value,
default_length
FROM
dba_tab_columns
WHERE
OWNER = ?
AND table_name = ?;
# index
SELECT
lower(index_name) index_name,
index_type type
FROM
dba_indexes
WHERE
OWNER = ?
AND table_name = ?
AND index_name NOT LIKE 'SYS_IL%';
SELECT
lower(column_name) column_name,
column_position,
descend
FROM
dba_ind_columns
WHERE
table_owner = ?
AND table_name = ?
AND index_name = ?;
#collect description
SELECT
comments
FROM
dba_tab_comments
WHERE
OWNER = ?
AND table_name = ?;
SELECT
lower(column_name) column_name,
comments
FROM
dba_col_comments
WHERE
OWNER = ?
AND table_name = ?;
#database
SELECT
lower(username) username
FROM
dba_users
WHERE
username NOT IN (
'STDBYPERF',
'READONLY',
'APPQOSSYS',
'ANYSQL',
'DBFLASH',
'SYS',
'SYSTEM',
'MONITOR',
'TBSEARCH',
'MANAGER',
'SYSMAN',
'EXFSYS',
'WMSYS',
'DIP',
'TSMSYS',
'ORACLE_OCM',
'OUTLN',
'DBSNMP',
'PERFSTAT',
'SEARCH',
'TOOLS',
'TBDUMP',
'DMSYS',
'XDB',
'ANONYMOUS',
'DEV_DDL'
);
#segsize
SELECT
round(sum(bytes) / 1024 / 1024, 0) mbytes
FROM
dba_segments
WHERE
OWNER = ?
AND segment_name = ?;