原文地址：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_nameFROM information_schema.schemataWHERE 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_capacityFROM information_schema.TABLESWHERE 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_scaleFROM information_schema.COLUMNSWHERE table_schema = 'employees'AND table_name = 'employees'; ## 查看某一个库下某一张表的索引 SELECT DISTINCT lower(index_name) index_name, lower(index_type) typeFROM information_schema.statisticsWHERE table_schema = 'employees'AND table_name = 'employees'; ## 查看某一个库下某一张表的某一个索引 SELECT lower(column_name) column_name, seq_in_index column_positionFROM information_schema.statisticsWHERE table_schema = 'employees'AND table_name = 'employees'AND index_name = 'primary'; ## 查看某一个库下某一个表的注释SELECT table_comment commentsFROM information_schema.TABLESWHERE table_schema = 'employees'AND table_name = 'employees'; ## 查看某一个库下某一个表的列的注释SELECT lower(column_name) column_name, column_comment commentsFROM COLUMNSWHERE 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_capacityFROM dba_tables aWHERE 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_lengthFROM dba_tab_columnsWHERE OWNER = ?AND table_name = ?; # indexSELECT lower(index_name) index_name, index_type typeFROM dba_indexesWHERE OWNER = ?AND table_name = ?AND index_name NOT LIKE 'SYS_IL%'; SELECT lower(column_name) column_name, column_position, descendFROM dba_ind_columnsWHERE table_owner = ?AND table_name = ?AND index_name = ?; #collect descriptionSELECT commentsFROM dba_tab_commentsWHERE OWNER = ?AND table_name = ?; SELECT lower(column_name) column_name, commentsFROM dba_col_commentsWHERE OWNER = ?AND table_name = ?; #databaseSELECT lower(username) usernameFROM dba_usersWHERE 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' ); #segsizeSELECT round(sum(bytes) / 1024 / 1024, 0) mbytesFROM dba_segmentsWHERE OWNER = ?AND segment_name = ?;
?
?