Query to Find Column(s) Name From All Tables of Database

The following script 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 '%EmpName%'
ORDER BY schema_name, table_name

If you want to find all the column name from your database run following script.

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
ORDER BY schema_name, table_name

Note: You can down any condition in WHERE clause to get desired result.