How to check Table depended view's procedure

Question

Hi ,

i have table student,fields are student_id, student_name, year_joining ...

now i want to rename the year_joining to year_completion ,

but i am not sure, How many view,procedure functions depend this year_joinging ,

In mysql, is there any function do that...

Answer 1

You might try querying the information_schema database:

USE `information_schema`;
SELECT * FROM `VIEWS` WHERE `VIEW_DEFINITION` LIKE '%year_joining%';
SELECT * FROM `ROUTINES` WHERE `ROUTINE_DEFINITION` LIKE '%year_joining%';

This should return a list of views/routines which reference the field year_joining.

I think I'm right in saying that, in either case, the column TABLE_NAME will contain the name of the view/procedure, and the column VIEW_DEFINITION or ROUTINE_DEFINITION will contain the definition of the view/procedure.

To restrict the search to a particular database, include TABLE_SCHEMA or ROUTINE_SCHEMA in your query, e.g.

SELECT * FROM `VIEWS` WHERE `VIEW_DEFINITION` LIKE '%year_joining%' AND `TABLE_SCHEMA` = 'my_database';
SELECT * FROM `ROUTINES` WHERE `ROUTINE_DEFINITION` LIKE '%year_joining%' AND `ROUTINE_SCHEMA` = 'my_database';