Sp_objectMYSQL get the content of the specified object and definition statement in the current instance _ MySQL

Sp_objectMYSQL obtains the specified object and definition statement content in the current instance bitsCN.com

Sp_object MYSQL obtains the content of the specified object and definition statement in the current instance.

[SQL]

DELIMITER $

USE 'test' $

Drop procedure if exists 'sp _ object' $

Create procedure 'sp _ object' (p_OBJECTNAME VARCHAR (255), p_DBNAME VARCHAR (255 ))

BEGIN

/*

Author: Chen Enhui

Call example:

CALL sp_object ('updatefactadgroupdailyusagebyhourly ','');

*/

-- Process and function

SELECT 'type' AS _ type, db as dbname, 'name' as objectname, body AS DEFINITION

FROM mysql. proc

WHERE db like concat (p_DBNAME, '% ')

AND 'name' like concat (p_OBJECTNAME, '%') -- AND 'type' = 'processed'

-- Table

UNION ALL

SELECT 'table' AS _ TYPE, TABLE_SCHEMA, TABLE_NAME, ''AS DEFINITION

FROM information_schema.TABLES

WHERE TABLE_SCHEMA like concat (p_DBNAME, '% ')

AND table_name like concat (p_OBJECTNAME, '% ')

-- Trigger

UNION ALL

SELECT 'trigger' AS _ TYPE, TRIGGER_SCHEMA as dbname, TRIGGER_NAME, ACTION_STATEMENT as definition from information_schema. 'trigger'

WHERE TRIGGER_SCHEMA like concat (p_DBNAME, '% ')

AND TRIGGER_NAME like concat (p_OBJECTNAME, '% ')

-- View

UNION ALL

SELECT 'View' AS _ TYPE, TABLE_SCHEMA as dbname, TABLE_NAME AS 'viewname', VIEW_DEFINITION as definition from information_schema. 'Views 'a

WHERE TABLE_SCHEMA like concat (p_DBNAME, '% ')

AND TABLE_NAME like concat (p_OBJECTNAME, '% ')

Order by _ TYPE, DBNAME;

END $

DELIMITER;

BitsCN.com