mysQl存储过程取得表数据_mysql存储过程,获取指定数据库的某个表的字段信息

DROP PROCEDURE IF EXISTS Proc;

DELIMITER //

CREATE PROCEDURE Proc(database_name varchar(50),table_name_ varchar(50))

BEGIN

DROP table IF EXISTS fk_view;

DROP table IF EXISTS col_view;

CREATE  table fk_view (

SELECT

b.TABLE_NAME 表,

b.COLUMN_NAME AS 栏位,

b.REFERENCED_TABLE_NAME AS 参考表,

b.REFERENCED_COLUMN_NAME AS 参考栏位

FROM

information_schema.KEY_COLUMN_USAGE as b

WHERE

b.TABLE_Schema = database_name

AND b.table_name = table_name_ and  (REFERENCED_TABLE_NAME<>'' or CONSTRAINT_name='PRIMARY')

);

CREATE  table col_view (

SELECT

table_name 表名,

COLUMN_NAME 字段名,

COLUMN_type 字段类型

FROM

information_schema.COLUMNS

WHERE

TABLE_Schema = database_name

AND table_name IN (

SELECT

table_name

FROM

information_schema.TABLES

WHERE

table_type = 'BASE TABLE'

AND TABLE_Schema = database_name

)

AND table_name = table_name_

);

select * from col_view as a LEFT JOIN fk_view as b on b.`栏位`=a.`字段名`;

DROP table IF EXISTS fk_view;

DROP table IF EXISTS col_view;

END//

DELIMITER ;

CALL Proc('hjf_jianzheng','sy_user');