mysql 获取数据库表、字段存储过程

 

DELIMITER $$

DROP PROCEDURE IF EXISTS `paltform`.`pt_init` $$
CREATE PROCEDURE `platform`.`pt_init` ()
BEGIN

/* Clear the table TablesRemark which the table has being delete from sys tables */
DELETE FROM tablesremark
  WHERE tablename NOT IN (
    SELECT t.table_name AS tablename
      FROM information_schema.tables t
      WHERE t.table_schema = 'paltform');

/* Check the sys tables,add the tables
   which does exist in the sys tables
   but does not exist in the table TablesRemark */
INSERT INTO tablesremark(tablename)
  SELECT LCASE(t.table_name) AS tablename
    FROM information_schema.tables t
      WHERE t.table_schema = 'platform'
    AND t.table_name NOT IN (SELECT tablename FROM tablesremark);

/* Clear the table ColsRemark which the table_column has being delete from the sys tables,columns */
CREATE TEMPORARY TABLE temp
  SELECT CONCAT(CONCAT(LCASE(t.table_name) ,'_'), LCASE(c.column_name)) AS tablecolname
    FROM information_schema.tables t
    INNER JOIN information_schema.columns c
          ON t.table_name = c.table_name
          WHERE t.table_schema = 'platform';
DELETE FROM colsremark WHERE id not IN (SELECT tablecolname FROM temp);
DROP TABLE temp;

/* Add the table_column data into the table ColsRemark
   which does exist in the sys tables,columns
   but does not exist in the table ColsRemark */
INSERT INTO ColsRemark (id , tablename, colname, colorder, addtime)
  SELECT CONCAT(CONCAT(LCASE(t.table_name) ,'_'), LCASE(c.column_name)) AS id,
         LCASE(t.table_name) AS tablename,
         LCASE(c.column_name) AS colname,
         c.ordinal_position AS colorder,
         now() AS addtime
    FROM information_schema.tables t
    INNER JOIN information_schema.columns c
          ON t.table_name = c.table_name
          WHERE t.table_schema = 'platform'
          AND (
            NOT EXISTS
                (SELECT id FROM colsremark col
                  WHERE col.tablename = t.table_name and col.colname = c.column_name
                )
          );

/* Update the table TablesRemark's column colchanged
   if the col has be changed in the table ColsRemark */
UPDATE tablesremark SET colchanged = 1
  WHERE tablename IN (SELECT tablename FROM colsremark WHERE colchanged=1);

/* Update the table ColsRemark's column colorder
   so that the order can as the same as the sys columns
   optimized sql */
UPDATE colsremark,(
 SELECT c.column_name,t.table_name,c.ordinal_position 
	FROM information_schema.columns c,information_schema.tables t 
	WHERE c.table_name=t.table_name
) temp 
	SET colsremark.colorder=temp.ordinal_position 
	WHERE temp.column_name=colsremark.colname;
/* not optimized sql
UPDATE colsremark SET colorder =
  (SELECT c.ordinal_position
    FROM information_schema.columns c
    INNER JOIN information_schema.tables t
    ON c.table_name=t.table_name
    WHERE c.column_name=colsremark.colname
    AND t.table_name=colsremark.tablename);
*/

UPDATE colsremark SET categoryid = '{612F4576-4BF6-424A-BB92-04E836CAB4FF}' WHERE colname IN ('adder' , 'moder');
UPDATE colsremark SET categoryid = '{0ECC56D4-9F55-4633-8A41-172CB220ABF9}' WHERE colname IN ('delstatus');
UPDATE colsremark SET categoryid = '{57460F9A-7F41-4733-84F5-AB1DDEAFBB0F}' WHERE colname IN ('rdeptid');

END $$

DELIMITER ;