Tag Archives: stored procedure

05 Dec

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;

drop temporary table if exists objects;
create temporary table objects
(
object_type varchar(100),
object_name varchar(100),
object_schema varchar(100)
)
engine=myisam;

INSERT INTO objects
/* query for triggers */
(SELECT ‘TRIGGER’,TRIGGER_NAME ,TRIGGER_SCHEMA
FROM information_schema.triggers
WHERE TRIGGER_SCHEMA like DB)

UNION

/* query for views*/
(SELECT ‘VIEW’, TABLE_NAME,TABLE_SCHEMA
FROM information_schema.tables
WHERE table_type=’VIEW’ and TABLE_SCHEMA like DB)

UNION

/* query for procedure*/
(SELECT ‘PROCEDURE’, SPECIFIC_NAME, ROUTINE_SCHEMA
FROM information_schema.routines
WHERE routine_type=’PROCEDURE’ and ROUTINE_SCHEMA like DB)

UNION

/* query for function*/
(SELECT ‘FUNCTION’, SPECIFIC_NAME, ROUTINE_SCHEMA
FROM information_schema.routines
WHERE routine_type=’FUNCTION’ and ROUTINE_SCHEMA like DB)

UNION

/* query for tables*/
(SELECT concat(ENGINE,’ TABLE’), TABLE_NAME, TABLE_SCHEMA
FROM information_schema.tables
WHERE table_type=’BASE TABLE’ and TABLE_SCHEMA like DB
GROUP BY ENGINE, TABLE_NAME);

/* show gathered information from temporary table */
SELECT object_name,object_type,object_schema
FROM objects;

/* Prepare and show summary */
SELECT object_schema AS `DATABASE`,
SUM(IF(object_type like ‘%TABLE’, 1, 0)) AS ‘TABLES’,
SUM(IF(object_type=’VIEW’, 1, 0)) AS ‘VIEWS’,
SUM(IF(object_type=’TRIGGER’, 1, 0)) AS ‘TRIGGERS’,
SUM(IF(object_type=’FUNCTION’, 1, 0)) AS ‘FUNCTIONS’,
SUM(IF(object_type=’PROCEDURE’, 1, 0)) AS ‘PROCEDURES’
FROM objects
GROUP BY object_schema;

END $$

DELIMITER ;

I have also published this as an article on EE.
http://www.experts-exchange.com/articles/Database/MySQL/Finding-tables-views-functions-procedures-and-triggers-in-MySQL-Database.html

I have also published this as an article on EE.

http://www.experts-exchange.com/articles/Database/MySQL/Finding-tables-views-functions-procedures-and-triggers-in-MySQL-Database.html

05 Dec

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:

source-table

And we want output as follows:

output-table

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

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 i INT;
DECLARE split_id INT;
DECLARE ins_query VARCHAR(500);
DECLARE splitter_cur CURSOR FOR
SELECT id,cat from tmp;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

DROP TEMPORARY TABLE IF EXISTS `my_splits`;
CREATE TEMPORARY TABLE `my_splits` (
`splitted_column` varchar(45) NOT NULL,
`id` int(10) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

OPEN splitter_cur;
splitter_loop:LOOP
FETCH splitter_cur INTO split_id,split_string;

SET my_delimiter=’;’;
SET occurance=length(split_string)-length(replace(split_string,my_delimiter,”))+1;
IF done=1 THEN
LEAVE splitter_loop;
END IF;
#  select occurance;
IF occurance > 0 then
#select occurance;
set i=1;
while i <= occurance do
#        select concat(“SUBSTRING_INDEX(SUBSTRING_INDEX( ‘”,split_string ,”‘, ‘”,my_delimiter,”‘, “,i, “),'”,my_delimiter,”‘,-1);”);
SET ins_query=concat(“insert into my_splits(splitted_column,id) values(“, concat(“SUBSTRING_INDEX(SUBSTRING_INDEX( ‘”,split_string ,”‘, ‘”,my_delimiter,”‘, “,i, “),'”,my_delimiter,”‘,-1),”,split_id,”);”));
#    select ins_query;
set @ins_query=ins_query;
PREPARE ins_query from @ins_query;
EXECUTE ins_query;
set i=i+1;
end while;
ELSE
set ins_query=concat(“insert into my_splits(splitted_column,id) values(“,split_string,”‘,”,split_id,”);”);
set @ins_query=ins_query;
PREPARE ins_query from @ins_query;
EXECUTE ins_query;
END IF;
set occurance=0;
END LOOP;

CLOSE splitter_cur;

END $$

DELIMITER ;

Usage: call split_string();

05 Dec

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

16 Nov

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).
Continue Reading…

29 Oct

Search / find through all databases, tables, columns in MySQL

What will you do if one day some one ask you to find single string in all databases, all tables and in all columns, In MySQL Database?

I just read such question and tried to find a “ready made” solution. Reusability is Key Concept 😉 !!

But I ended up finding no “copy-paste” material. Some of the posts like http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm helped me out and supported my thinking of “how to do it” !

Here is the idea for how to search through all databases – tables – columns:

  • Create a table for storing output.
  • Loop through information_schema database’s COLUMNS table to obtain alldatabases, table and column names.
  • Execute a count(*) query on database.table for each column with appropriate search string in where condition.
  • If count(*) > 0, that perticular column has the search term.
  • Insert that triplet (database name, table name, column name) in to a table.
  • Select * from table to view respective database,table and column names having the search term.

MySQL Procedure for search in all fields of all databases

Read More

-- Kedar Vaijanapurkar --