Mysql views the table structure and existing index information

The background for mysql to view the table structure and existing index information is to give a table name and the corresponding table structure information and index information. Common commands are as follows: desc tableName; desc employees. employees; www.2cto.com show columns from tableName; show COLUMNS from employees. employees; describe tableName; DESCRIBE employees. employees; the three display results are the same. The filed, type, null, key, default, and extra values are displayed in the table. Show create table tableName; show create table employees. employees; this statement displays the table creation statement for this TABLE. Select * from columns where table_name = 'table name ';
Select * from information_schema.COLUMNS where TABLE_SCHEMA = 'ployees' and TABLE_NAME = 'ployees'; the displayed results are complete. Next, you can use a more comprehensive SQL statement to synchronize all the SQL statements in the mysql and orac data dictionaries. Mysql part: 01 # view all databases 02SELECT03 lower (schema_name) schema_name04 www.2cto.com FROM05 using schema_name not in (08 'mysql', 09 'information _ scheme', 10' test ', 11 'search', 12 'tbsearch', 13 'sbtest', 14 'dev _ ddler' 15) 16 17 # Check all the tables in a database, 18SELECT19 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_len Bytes/1024/1024) store_capacity26FROM27 export table_schema = 'ployees' 30AND table_name not like 'tmp # _ % 'escape '#'31 32 # view all fields of a table in a database 33SELECT34 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 bytes data_length, 41 Numeric_precision data_precision, 42 numeric_scale data_scale43FROM44 information_schema.COLUMNS45 www.2cto.com WHERE46 table_schema = 'ployees' 47AND table_name = 'ployees '; 48 49 50 # view the index of a table under a database 51 52 SELECT DISTINCT53 lower (index_name) index_name, 54 lower (index_type) type55FROM56 information_schema.statistics57WHERE58 table_schema = 'ployees' 59AND table_name = 'ployees'; 60 61 # view a database A table index 62 63SELECT64 lower (column_name) column_name, 65 seq_in_index column_position66FROM67 partition table_schema = 'ployees' 70AND table_name = 'ployees' 71AND index_name = 'primary '; 72 www.2cto.com 73 # view the comment 74SELECT75 table_comment comments76FROM77 comment table_schema = 'ployees' 80 and table_name = 'ployees'; 81 82 # view a table under a database Comment 83SELECT84 lower (column_name) column_name, 85 column_comment comments86FROM87 COLUMNS88WHERE89 table_schema = 'ployees' 90AND table_name = 'ployees'; oracle part: www.2cto.com 001 # table structure: 002SELECT003 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 SELECT012 ceil (sum (bytes)/102 4/1024) 013 FROM014 dba_segments b015 WHERE016 a. OWNER = B. OWNER017 AND a. table_name = B. segment_name018) AS your dba_tables a021WHERE022 OWNER =? 023AND table_name not like 'tmp % '; 024 lower (column_name) column_name, 027 column_id position, 028 data_type, 029 data_length, 030 data_precision, 031 data_scale, 032 rows, 033 data_default values, 034 www.2cto.com default_length035FROM036 dba_tab_columns037WHERE038 OWNER =? 039AND table_name = ?; 040 041 # index042SELECT043 lower (index_name) index_name, 044 index_type type045FROM046 dba_indexes047WHERE048 OWNER =? 049AND table_name =? 050AND index_name not like 'sys _ IL % '; 051 052SELECT053 lower (column_name) column_name, 054 column_position, 055 descend056FROM057 dba_ind_columns058WHERE059 table_owner =? 060AND table_name =? 061AND index_name = ?; 062 www.2cto.com 063 # collect description064SELECT065 comments066FROM067 dba_tab_comments068WHERE069 OWNER =? 070AND table_name = ?; 071 072SELECT073 lower (column_name) column_name, 074 comments075FROM076 dba_col_comments077WHERE078 OWNER =? 079AND table_name = ?; 080 081 # database082SELECT083 lower (username) login Login username not in (088 'stdbyperf', 089 'readonly', 090 'appqosss', 091 'anysql', 092 'dbflash ', 093 'sys ', 094 'system', 095 'Monitor', 096 'tbsearch', 097 'manager', 098 www.2cto.com 'sysmanc', 099 'exfsys ', 100 'wmsys ', 101 'dip', 102 'tsmsys ', 103 'oracle _ occm', 104 'outln ', 105 'dbsnmp', 106 'perfstat ', 107 'search', 108 'tool', 109 'Tbdump', 110 'dmsys ', 111 'xdb', 112 'anonus us', 113 'dev _ ddls' 114); 115 116 # segsize117SELECT118 round (sum (bytes) /1024/1024, 0) mbytes119FROM120 www.2cto.com dba_segments121WHERE122 OWNER =? 123AND segment_name = ?; For more information about segements in oralce, see this series of articles. http://book.51cto.com/art/201108/288137.htm To sum up, the fields in the mysql database and table are all viewed in information_schemal. These are essential SQL statements for obtaining data dictionaries.
Mysql statements in this article have been tested locally. In addition, you must be familiar with the oracle structure. Author Ji ziming