I was trying write a procedure with some logic and in that scenario have to find out that how many table contain column-name like “sales” in my database. In my view there is two solution for this may be it does more than two. First solution is search in every single table manually. But there is a problem. Suppose if i have more than 1000 tables in my database then this is painful for me. So i drop this idea and trying to find out some easy way and then i find another way.
Write the following query and get the output.
select table_name,column_name from information_schema.columnswhere column_name like '%sales%'
You can also find out store procedure as well
select routine_name, routine_definition from information_schema.routines where routine_definition like '%tax_id%' and routine_type='procedure'