Well procedures mainly carried out working with information schema and it’s usage in stored procedure. Procedures are fairly simple and easy to understand.
1. Edit_table – following procedure executes queries to particular table if it exists.
Basically I created it to satisfy a need of altering a table if column exists.
Now it can be used to execute any query if table exists.
Usage: call Edit_table(database-name,table-name,query-string);
– Procedure will check for existence of table-name under database-name and will execute query-string if it exists.
Download Stored Procedure: Edit_table
DELIMITER $$
DROP PROCEDURE IF EXISTS `Edit_table` $$
CREATE PROCEDURE `Edit_table` (in_db_nm varchar(20),in_tbl_nm varchar(20),in_your_query varchar(200))
DETERMINISTIC
BEGIN
DECLARE var_table_count INT;
select count(*) INTO @var_table_count from information_schema.TABLES where TABLE_NAME=in_tbl_nm and TABLE_SCHEMA=in_db_nm;
IF (@var_table_count > 0) THEN
SET @in_your_query = in_your_query;
#SELECT @in_your_query;
PREPARE my_query FROM @in_your_query;
EXECUTE my_query;
ELSE
select "Table Not Found";
END IF;
END $$
DELIMITER ;
1A. A slight variation of this procedure is editing a table if column exists.
Download Stored Procedure: Edit_table_column
DELIMITER $$
DROP PROCEDURE IF EXISTS `Edit_table_column` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `Edit_table_column`(in_db_nm varchar(20),in_tbl_nm varchar(20),in_colm_nm varchar(20),in_your_query varchar(200))
DETERMINISTIC
BEGIN
DECLARE var_table_count INT;
select count(*) INTO @var_table_count from information_schema.COLUMNS where TABLE_NAME=in_tbl_nm and TABLE_SCHEMA=in_db_nm and COLUMN_NAME=in_colm_nm;
IF (@var_table_count > 0) THEN
SET @in_your_query = in_your_query;
SELECT @in_your_query;
PREPARE my_query FROM @in_your_query;
EXECUTE my_query;
ELSE
select "Table Not Found";
END IF;
END $$
DELIMITER ;
Usage: call Edit_table_column(database-name,table-name,column-name,query-string);