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');

转载于:https://www.cnblogs.com/gaocong/p/5127003.html