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

程序员希振技术 2022年11月23日

0 收藏 516 点赞 4,517 浏览 1040 个字

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 ;