How to find column name within all tables in sql server


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'

Tags: , ,