mysql获取数据库存储过程_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 ;

分享到：

2012-03-15 12:55

浏览 1195

分类:数据库

评论