SQL SERVER – Query to Find Column From All Tables of Database

1. To get Simply the table names for a column name

SELECT name FROM sysobjects WHERE id IN ( SELECT id FROM syscolumns WHERE name like '%your_column_name%' )

or else by schema and more detail

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 '%your_column_name%'
ORDER BY schema_name, table_name;

References:
1)http://blog.sqlauthority.com/2008/08/06/sql-server-query-to-find-column-from-all-tables-of-database/

2.Retrieve columnnames of all tables in a SQL Server database

a)In SQL-Server 2005+ you can do it using system views sys.columns and sys.tables

SELECT t.name TableName, c.name ColumnName
FROM sys.tables t
     JOIN sys.columns c ON t.object_id=c.object_id

b)Before SQL SERVER 2005 you can use below

SELECT   SysObjects.[Name] as TableName,   
    SysColumns.[Name] as ColumnName,   
    SysTypes.[Name] As DataType,   
    SysColumns.[Length] As Length   
FROM   
    SysObjects INNER JOIN SysColumns   
ON SysObjects.[Id] = SysColumns.[Id]   
    INNER JOIN SysTypes  
ON SysTypes.[xtype] = SysColumns.[xtype]  
WHERE  SysObjects.[type] = 'U'  
ORDER BY  SysObjects.[Name]