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.

[ad#ad-2-300×250]

Usage: call xplore(database-name);
– Procedure will search through information schema for database objects under database-name.

Download Stored Procedure: explore-database
[ad#lnkunt-468×15]

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

One thought on “Stored procedure to Find database objects

  1. Visual Studio and SQL Server management Studio are two different entities. So it takes a lot of time when we try an interaction with the database from Visual Studio then it takes a lot of time.

    When we are using desktop applications then we do not realize this time delay, but while using web applications the time delay becomes an important factor because the database is on the remote server so a huge time delay is observed.

    Normally, the query that we write in Visual Studio is first compiled and then executed in Sql Server which then returns the results. So, this is why a time delay is observed.

    The answer to this is Stored Procedure usage. The Stored Procedure is stored in compiled format in SQL server so when we call it from Visual Studio application then it is just executed. Thus saving a lot of time.

    An illustration of proper usage of STORED PROCEDURE has been displayed in the following video. You may see it:

    http://www.visiontechno.net/studymats/storeprocedure.html

Leave a Reply

Your email address will not be published. Required fields are marked *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

-- Kedar Vaijanapurkar --