SQL Server: how to search for a table name or a column name or a given stored procedure name

This article demonstrates how the Microsoft SQL Server database can be queried to perform the following tasks:

1. fetch a list of tables.
2. fetch a list of Columns.
3. fetch a list of Stored Procedures

The results can refined further by including other search criteria in the where clause of each query.

--Query to fetch the list of table that match a pattern
select * from information_schema.tables where table_name like '%Employee%'

--Query to fetch the list of Columns (with table info) that match a pattern.
select * from information_schema.columns where column_name like '%EmpId%'

--Query to fetch the list of Stored Procedures that match a pattern.
select ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_DEFINITION from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE='PROCEDURE' and routine_name like '%MyProc%'

PS: Sql Authority is an excellent blog by Pinal Kumar Dev of SQL Server related info.

Reference:
[1] MSDN Online