Search for objects in mysql

Question:

Good afternoon, I'm using WorkBeanch 6.3 and I need to search the MySql database if a given Column exists and what is its Table, I also need to know if the given Column is used in a Procedure or View, for example in Sql Server I do this;

      SELECT O.name as Tabelas 
        FROM syscolumns C 
  INNER JOIN sysobjects O ON C.id = O.id 
       WHERE c.name like '%idConta%'

Answer:

You could try it like this, where the like passes the idConta

SELECT SPECIFIC_NAME FROM information_schema.routines WHERE ROUTINE_DEFINITION like '%idConta%'

To discover object definers, work on the 'information_schema' table, where you get the object information, you will get a lot from it. Below are examples of select to discover the definers.

SELECT specific_name, routine_schema, routine_name, routine_type, definer FROM information_schema.routines WHERE definer = 'seu_user'; 

SELECT trigger_catalog, trigger_schema, trigger_name, definer FROM information_schema.triggers WHERE definer = 'seu_user';

SELECT table_catalog, table_schema, table_name, definer FROM information_schema.views WHERE definer = 'seu_user';
Scroll to Top
AllEscort