Search For Columns in SQL Server

In this post i would like to show you various methods to search for columns in SQL Server metadata tables.


Method 1:
========

SELECT TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE
'%emp%';

Method 2:
========

SELECT OBJECT_SCHEMA_NAME(object_id) AS SCHEMANAME,
OBJECT_NAME(object_id) OBJECTNAME,
name as column_name
FROM
sys.columns
WHERE name LIKE '%emp%';

I normally use Method 1, to find the information. Please let me know if you have any other approach.....