Looking for Tables and Columns in SQL Server 2012

In SQL Server 2012, sys.columns is a view you can find in the node System Views in the msdb database.

The various columns in sys.views are shown in this next image. You can find everything about the Columns in this view.



If you know the ObjectID of the Column of a table in the database, you can find its name from this query, the intellisense gives you an idea of this selection.

SysColumnName.png

A typical query and its output are as shown for finding the column name from ObjectID.

SysColumnName_02.png

If you know the Column_Name then you can find Column_ID, the intellisense gives you an idea of this selection.

SysColumnID.png

A typical query and its output are as shown for finding Column ID from Column Name.

SysColumnID_02.png

Starting from table name you can get the columns from these queries:

You can also get the ObjectID of the table from this query:

Use Northwind
go
Select *
From sys.tables
where tables.name='Employees'
go

The above query returns one value=245575913
Using the above you can get a whole lot of information for the columns from this query:

Select *
from sys.columns
where Columns.object_id=245575913