How to Search a Column Name Within All Tables of a Database and How to Search Stored Procedures Containing a Particular Text

1. How I find a particular column name within all tables of SQL server database?

For example, I have a database named Organisation. I have more than one table where tax_id column is present.

Most of the time, we have to find such a column from the whole database. The solution is provided below:

select table_name,column_name from information_schema.columns
where column_name like '%tax%'

Here, I am searching for column_name which contains tax within its name. It will return all the tables and respective columns containing tax.

Example:

Table_Name	Column_name
t_Customer	tax_id
t_Employee	tax_number
t_Balance_sheet	tax_percentage

2. How to search stored procedures containing a particular text?

Below is the solution. Suppose I need to find tax_id from all stored procedures. The below query will return all stored procedures containing text tax_id.

select routine_name, routine_definition
      from information_schema.routines
      where routine_definition like '%tax_id%'
      and routine_type='procedure'