SQL : Search the Column in the tables or Serach text in the store procs

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

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 '%Employee%'

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

Example:

Table_Name	Column_name
Employee	Employee_id
EmployeeSalary	Employee_id
EmployeeAttendance	Employee_id

 

Now I want more complex like it will return all the tables, respective, schema name columns containing Employee which will go return all the tables containing specific column along with their schema name

 

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%EmployeeID%'
ORDER BY schema_name, table_name;

 

 

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

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

Employee

.

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