stored procedure

Stored procedure to add-remove prefix by rename table mysql

Here is one more procedure – (this time) for mass renaming of table. Adding and Removing table-name prefixes 🙂 A friend of mine was renaming 100+ tables by using replace methods in notepad. I showed em a bit better editor: Editplus and then I thought of rescue rest of those who are still interested in some techie-solution to such things. This procedure is outcome of that try and its as usual very simple one with two functionalities: rename mysql table of a database by adding prefix rename mysql table of a database by removing prefix So below mysql stored procedure...

Stored procedure to Find database objects

This procedure lists available database objects under passed database name. It lists present Tables, Views, Stored Procedures, Functions and Triggers under particular database. It also lists storage engine of tables. It uses information schema database to gather information and storing in a temporary table. Usage: call xplore(database-name); – Procedure will search through information schema for database objects under database-name. Download Stored Procedure: explore-database DELIMITER $$ DROP PROCEDURE IF EXISTS `xplore` $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `xplore`(IN_DB varchar(100)) BEGIN DECLARE DB VARCHAR(100); DECLARE NO_TABLES INT(10); DECLARE NO_VIEWS INT(10); DECLARE NO_FUNCTIONS INT(10); DECLARE NO_PROCEDURES INT(10); DECLARE NO_TRIGGERS INT(10); DECLARE SUMMARY VARCHAR(200); SET DB=IN_DB;...

MySQL Stored procedure – Split Delimited string into Rows

This procedure will split  a “;” separated column in to new fields preserving ids. This is very specific problem, lets check it with example. Consider a sample table test: And we want output as follows: So again I wrote a procedure. Procedure will read a data from “tmp” table and will split data by my_delimiter delimiter and generate a temporary table. You may alter it as per requirement. Download Stored Procedure : split_string null DELIMITER $$ DROP PROCEDURE IF EXISTS `split_string` $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `split_string`() BEGIN DECLARE my_delimiter CHAR(1); DECLARE split_string varchar(255); DECLARE done INT; DECLARE occurance INT; DECLARE...

MySQL Stored procedure – Execute query if table or Column exists

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...

Calculate Mysql Memory Usage – Quick Stored Procedure

In this post we will look into the MySQL memory utilization estimation or calculation based on the global variables. Using a simple stored procedure call you can get the memory usage estimation for the present MySQL instance. We have global buffers which are allocated irrespective of connections as and when mysql server is started. Along with that mysql server allocates memory to each thread to perform respective tasks. So the formula goes: Mysql Server Memory Usage = Sum of Global Buffers + (number of Connection * Per thread memory variables).