最近，在写一个代码工具，从数据读取表生成实体Bean、SQLMap，需要获取到数据库的元信息，为了适应不同的数据库，需要针对每一种数据库实现一种获取元信息的方式，这里是MySQL5.5元信息的获取方式：
 
1、获取一个数据库下面所有的表
SELECT t.table_name,t.table_comment,t.create_time, 
FROM information_schema.tables t 
WHERE t.table_schema =  SCHEMA();
 
2、获取一个表的元信息
SELECT t.column_name, 
             t.data_type, 
              CAST(SUBSTR(t.column_type, INSTR(t.column_type,  '(') + 1, INSTR(t.column_type, ')') - INSTR(t.column_type,  '(') - 1)  AS  CHAR(20)) data_length, 
              CAST(t.column_type  AS  CHAR(20)) column_type, 
             t.column_comment, 
              IF (t.is_nullable= 'YES',1,0) is_nullable, 
              IF (t.column_key =  'PRI', 1, 0) is_key 
FROM information_schema.columns t 
WHERE t.table_schema =  SCHEMA()  AND 
            t.table_name =  '表名' 
ORDER  BY t.ordinal_position;
 
3、获取一个表的建表语句
SHOW  CREATE  TABLE 表名;
 
或者另外一种方式：
CREATE    FUNCTION `get_tab_ddl`(p_tab_name  VARCHAR(100)) RETURNS  text    
        NO SQL 
        DETERMINISTIC 
BEGIN 
     DECLARE v_return  TEXT  DEFAULT  ''; 
     DECLARE v_ddl  TEXT  DEFAULT  ''; 
     
    #表信息相关变量 
     DECLARE v_engine  VARCHAR(255); 
     DECLARE v_row_format  VARCHAR(255); 
     DECLARE v_tab_comment  VARCHAR(255); 
    #约束相关变量 
     DECLARE v_cons_name  VARCHAR(255); 
     DECLARE v_cons_type  VARCHAR(255); 
     
    #约束列相关变量 
     DECLARE v_cons_col_name  VARCHAR(255); 
     DECLARE v_ref_tab_schema  VARCHAR(255); 
     DECLARE v_ref_tab_name  VARCHAR(255); 
     DECLARE v_ref_col_name  VARCHAR(255); 
     DECLARE v_update_rule     VARCHAR(255); 
     DECLARE v_delete_rule     VARCHAR(255); 
    #索引相关变更 
     DECLARE v_index_name  VARCHAR(255); 
     DECLARE v_l_index_name  VARCHAR(255)  DEFAULT  ''; 
     DECLARE v_ind_col_name  VARCHAR(255); 
     
     DECLARE v_done  INT  DEFAULT 0; 
     
    #列游标 
     DECLARE cur_column  CURSOR  FOR    
             SELECT CONCAT( '    ', '`',t.column_name, '` ',column_type, 
              IF(t.is_nullable =  'NO', ' NOT NULL', ''), 
              IF(t.extra  IS  NULL, '',CONCAT( ' ',t.extra)), 
              IF(t.column_default  IS  NULL, '',CONCAT( ' ', 'DEFAULT ' , " '" , t.column_default , "'")), 
              IF(t.column_comment =  '', '',CONCAT( ' ', 'COMMENT ' , " '" , t.column_comment , "'")), ',') tab_column 
             FROM information_schema.columns t 
          WHERE t.table_schema =  SCHEMA() 
              AND t.table_name = p_tab_name 
             ORDER  BY t.ordinal_position; 
    #主键约束 
     DECLARE cur_pk  CURSOR  FOR    
          SELECT t.column_name 
              FROM information_schema.key_column_usage t 
             WHERE t.table_schema =  SCHEMA() 
                 AND t.table_name = p_tab_name 
                 AND t.constraint_name =  'PRIMARY' 
              ORDER  BY t.ordinal_position; 
    #其它约束游标 
     DECLARE cur_cons  CURSOR  FOR    
          SELECT t.constraint_type,t.constraint_name 
              FROM information_schema.table_constraints t 
             WHERE t.table_schema =  SCHEMA() 
                 AND t.table_name = p_tab_name 
                 AND t.constraint_type <>  'PRIMARY KEY'; 
    #约束列游标 
     DECLARE cur_col_cons  CURSOR  FOR    
          SELECT t.column_name,t.referenced_table_schema,t.referenced_table_name,t.referenced_column_name,c.update_rule,c.delete_rule 
              FROM information_schema.key_column_usage t 
             LEFT  JOIN information_schema.referential_constraints c  ON (t.table_name = c.table_name  AND t.constraint_name = c.constraint_name) 
             WHERE t.table_schema =  SCHEMA() 
                 AND t.table_name = p_tab_name 
                 AND t.constraint_name = v_cons_name 
              ORDER  BY t.ordinal_position; 
    #表上索引游标 
     DECLARE cur_index  CURSOR  FOR    
          SELECT t.index_name,t.column_name 
              FROM information_schema. statistics t 
             WHERE t.table_schema =  SCHEMA() 
                 AND t.table_name = p_tab_name 
                 AND  NOT  EXISTS ( SELECT 1  FROM information_schema.table_constraints c 
                                                  WHERE t.table_schema = c.table_schema 
                                                      AND t.table_name = c.table_name 
                                                      AND t.index_name = c.constraint_name) 
             ORDER  BY t.index_name,t.seq_in_index; 
         
     DECLARE  CONTINUE HANDLER  FOR SQLSTATE  '02000'  SET v_done=1;    
        #表信息 
         SELECT  IF(t.engine =  ''  OR t.engine  IS  NULL, '',CONCAT( ' ENGINE=',t.engine)) ENGINE, 
                     t.row_format, 
                      IF(t.table_comment =  ''  OR t.table_comment  IS  NULL, '',CONCAT(" COMMENT= '",t.table_comment,"'")) table_comment 
                      INTO v_engine,v_row_format,v_tab_comment 
             FROM information_schema.tables t 
         WHERE t.table_schema =  SCHEMA() 
             AND t.table_name = p_tab_name;     
     
     
      SET v_return = CONCAT( 'CREATE TABLE `',p_tab_name, '` (', CHAR(13)); 
        
        
     #打开列游标 
      OPEN cur_column; 
      FETCH cur_column  INTO v_ddl; 
        
      WHILE v_done <> 1 DO 
        
             SET v_return = CONCAT(v_return,v_ddl, CHAR(13)); 
             
             FETCH cur_column  INTO v_ddl; 
             
      END  WHILE;    
      CLOSE cur_column; 
      SET v_ddl =  ''; 
        
     #打开主键约束 
      SET v_done = 0; 
      OPEN cur_pk ; 
      FETCH cur_pk  INTO v_cons_col_name; 
      WHILE v_done <> 1 DO    
              SET v_ddl = CONCAT(v_ddl, '`',v_cons_col_name, '`,'); 
              FETCH cur_pk  INTO v_cons_col_name; 
      END  WHILE; 
      CLOSE cur_pk; 
      IF v_ddl <>  ''  THEN    
             SET v_return = CONCAT(v_return, '    ', 'PRIMARY KEY (', LEFT(v_ddl,CHAR_LENGTH(v_ddl) - 1), '),', CHAR(13)); 
      END  IF; 
        
      SET v_return = CONCAT( LEFT(v_return,CHAR_LENGTH(v_return) - 2), CHAR(13)); 
      SET v_return = CONCAT(v_return, ') ',v_engine,v_tab_comment, ' ;', CHAR(13)); 
     #打开其它约束游标 
      SET v_done = 0; 
      OPEN cur_cons; 
      FETCH cur_cons  INTO v_cons_type,v_cons_name; 
      WHILE v_done <> 1 DO    
              IF v_cons_type =  'FOREIGN KEY'  THEN    
                     SET v_return = CONCAT(v_return, CHAR(13), 'ALTER TABLE `',p_tab_name, '` ADD CONSTRAINT `',v_cons_name, '` FOREIGN KEY ('); 
                    #打开外键约束列游标 
                     OPEN cur_col_cons; 
                     FETCH cur_col_cons  INTO v_cons_col_name,v_ref_tab_schema,v_ref_tab_name,v_ref_col_name,v_update_rule ,v_delete_rule; 
                     WHILE v_done <> 1 DO 
                             SET v_return = CONCAT(v_return, '`',v_cons_col_name, '`) REFERENCES `',v_ref_tab_name, '` (`',v_ref_col_name, '`) ', 
                                                                         'ON DELETE ',v_delete_rule, ' ON UPDATE ',v_update_rule); 
                             FETCH cur_col_cons  INTO v_cons_col_name,v_ref_tab_schema,v_ref_tab_name,v_ref_col_name,v_update_rule ,v_delete_rule;         
        
                     END  WHILE; 
                     CLOSE cur_col_cons; 
                     SET v_return = CONCAT(v_return, ';', CHAR(13)); 
                  ELSE    
                     SET v_return = CONCAT(v_return, CHAR(13), 'ALTER TABLE `',p_tab_name, '` ADD CONSTRAINT `',v_cons_name, '` UNQINE ('); 
                    #打开唯一约束列游标 
                     OPEN cur_col_cons; 
                     FETCH cur_col_cons  INTO v_cons_col_name,v_ref_tab_schema,v_ref_tab_name,v_ref_col_name,v_update_rule ,v_delete_rule; 
                     WHILE v_done <> 1 DO 
                             SET v_return = CONCAT(v_return, '`',v_cons_col_name, '`,'); 
                             FETCH cur_col_cons  INTO v_cons_col_name,v_ref_tab_schema,v_ref_tab_name,v_ref_col_name,v_update_rule ,v_delete_rule;                
                     END  WHILE; 
                     CLOSE cur_col_cons; 
                     SET v_return = CONCAT( LEFT(v_return,CHAR_LENGTH(v_return) - 1), ');', CHAR(13)); 
              END  IF; 
              SET v_done = 0; 
              FETCH cur_cons  INTO v_cons_type,v_cons_name; 
      END  WHILE;    
      CLOSE cur_cons; 
     #打开索引游标 
      SET v_done = 0; 
      SET v_ddl =  ''; 
        
      OPEN cur_index; 
      FETCH cur_index  INTO v_index_name,v_ind_col_name; 
      WHILE v_done <> 1 DO                
             IF v_index_name = v_l_index_name  THEN    
                  SET v_ddl = CONCAT(v_ddl, '`',v_ind_col_name, '`,'); 
            ELSEIF v_l_index_name  IS  NULL  OR v_l_index_name =  ''  THEN    
                  SET v_ddl = CONCAT(v_ddl, CHAR(13), 'CREATE INDEX `',v_index_name, '` ON `',p_tab_name, '` (`',v_ind_col_name, '`,'); 
                 ELSE    
                      SET v_ddl = CONCAT( LEFT(v_ddl,CHAR_LENGTH(v_ddl) - 1), ');', CHAR(13), CHAR(13), 'CREATE INDEX `', 
                                                                 v_index_name, '` ON `',p_tab_name, '` (`',v_ind_col_name, '`,'); 
             END  IF; 
                
             SET v_l_index_name = v_index_name; 
             FETCH cur_index  INTO v_index_name,v_ind_col_name; 
      END  WHILE;        
      CLOSE cur_index; 
      IF v_ddl <>  ''  THEN 
            
          SET v_return = CONCAT(v_return, LEFT(v_ddl,CHAR_LENGTH(v_ddl) - 1), ');', CHAR(13));                         
     
      END  IF; 
                                                     
     RETURN v_return; 
     
END