Search tables by using column name in SQL Server

Following query is used to find all tables and views which contain specific column name.

SELECT [TABLE NAME]=SYSOBJECTS.NAME,

[COLUMN NAME]=SYSCOLUMNS.NAME,
[DATA TYPE]=SYSTYPES.NAME,
[COLUMN LENGTH] = SYSCOLUMNS.LENGTH
FROM SYSOBJECTS
JOIN SYSCOLUMNS ON SYSOBJECTS.ID = SYSCOLUMNS.ID
JOIN SYSTYPES ON SYSCOLUMNS.XTYPE=SYSTYPES.XTYPE
WHERE SYSCOLUMNS.NAME ='COLUMN NAME'
 
You need to give column name

List of all Tables, Views and their columns in selected SQl database

Following query gives complete list of tables and views in a SQL database.

USE [DATABASE NAME]

GO
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
 
Following is the sample query to get column names and data types for selected table:
 
SELECT
COLUMN_NAME 'COLUMN NAME',
DATA_TYPE 'DATA TYPE',
COULMN_DEFAULT 'COLUMN DEFAULT'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'TABLE NAME'