{"id":311,"date":"2009-12-05T20:43:34","date_gmt":"2009-12-05T15:13:34","guid":{"rendered":"http:\/\/kedar.nitty-witty.com\/?p=311"},"modified":"2015-01-03T16:48:39","modified_gmt":"2015-01-03T16:48:39","slug":"stored-procedure-to-find-database-objects","status":"publish","type":"post","link":"https:\/\/kedar.nitty-witty.com\/blog\/stored-procedure-to-find-database-objects","title":{"rendered":"Stored procedure to Find database objects"},"content":{"rendered":"

This procedure lists available database objects under passed database name.<\/p>\n

It lists present Tables, Views, Stored Procedures, Functions and Triggers under particular database.
\nIt also lists storage engine of tables.
\nIt uses information schema database to gather information and storing in a temporary table.<\/p>\n

Usage: call xplore(database-name);
\n– Procedure will search through information schema for database objects under database-name.<\/p>\n

Download Stored Procedure: explore-database<\/a><\/strong><\/p>\n

DELIMITER $$<\/span><\/p>\n

DROP PROCEDURE IF EXISTS `xplore` $$
\nCREATE DEFINER=`root`@`localhost` PROCEDURE `xplore`(IN_DB varchar(100))
\nBEGIN
\nDECLARE DB VARCHAR(100);
\nDECLARE NO_TABLES INT(10);
\nDECLARE NO_VIEWS INT(10);
\nDECLARE NO_FUNCTIONS INT(10);
\nDECLARE NO_PROCEDURES INT(10);
\nDECLARE NO_TRIGGERS INT(10);
\nDECLARE SUMMARY VARCHAR(200);
\nSET DB=IN_DB;<\/span><\/p>\n

drop temporary table if exists objects;
\ncreate temporary table objects
\n(
\nobject_type varchar(100),
\nobject_name varchar(100),
\nobject_schema varchar(100)
\n)
\nengine=myisam; <\/span><\/p>\n

INSERT INTO objects
\n\/* query for triggers *\/
\n(SELECT ‘TRIGGER’,TRIGGER_NAME ,TRIGGER_SCHEMA
\nFROM information_schema.triggers
\nWHERE TRIGGER_SCHEMA like DB)<\/span><\/p>\n

UNION<\/span><\/p>\n

\/* query for views*\/
\n(SELECT ‘VIEW’, TABLE_NAME,TABLE_SCHEMA
\nFROM information_schema.tables
\nWHERE table_type=’VIEW’ and TABLE_SCHEMA like DB)<\/span><\/p>\n

UNION<\/span><\/p>\n

\/* query for procedure*\/
\n(SELECT ‘PROCEDURE’, SPECIFIC_NAME, ROUTINE_SCHEMA
\nFROM information_schema.routines
\nWHERE routine_type=’PROCEDURE’ and ROUTINE_SCHEMA like DB)<\/span><\/p>\n

UNION<\/span><\/p>\n

\/* query for function*\/
\n(SELECT ‘FUNCTION’, SPECIFIC_NAME, ROUTINE_SCHEMA
\nFROM information_schema.routines
\nWHERE routine_type=’FUNCTION’ and ROUTINE_SCHEMA like DB)<\/span><\/p>\n

UNION<\/span><\/p>\n

\/* query for tables*\/
\n(SELECT concat(ENGINE,’ TABLE’), TABLE_NAME, TABLE_SCHEMA
\nFROM information_schema.tables
\nWHERE table_type=’BASE TABLE’ and TABLE_SCHEMA like DB
\nGROUP BY ENGINE, TABLE_NAME);<\/span><\/p>\n

\/* show gathered information from temporary table *\/
\nSELECT object_name,object_type,object_schema
\nFROM objects;<\/span><\/p>\n

\/* Prepare and show summary *\/
\nSELECT object_schema AS `DATABASE`,
\nSUM(IF(object_type like ‘%TABLE’, 1, 0)) AS ‘TABLES’,
\nSUM(IF(object_type=’VIEW’, 1, 0)) AS ‘VIEWS’,
\nSUM(IF(object_type=’TRIGGER’, 1, 0)) AS ‘TRIGGERS’,
\nSUM(IF(object_type=’FUNCTION’, 1, 0)) AS ‘FUNCTIONS’,
\nSUM(IF(object_type=’PROCEDURE’, 1, 0)) AS ‘PROCEDURES’
\nFROM objects
\nGROUP BY object_schema;<\/span><\/p>\n

END $$<\/span><\/p>\n

DELIMITER ;<\/span><\/p>\n

<\/span><\/p>\n

<\/span><\/span><\/p>\n

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

I have also published this as an article on EE.<\/p>\n

http:\/\/www.experts-exchange.com\/articles\/Database\/MySQL\/Finding-tables-views-functions-procedures-and-triggers-in-MySQL-Database.html<\/p>\n","protected":false},"excerpt":{"rendered":"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.…\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[8,378,6],"tags":[24,427,108,426],"class_list":["post-311","post","type-post","status-publish","format-standard","category-mysql","category-mysql-scripts-mysql","category-technical","tag-code","tag-mysql","tag-stored-procedure","tag-technical"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t