SQL Cheat Sheet: Retrieving Column Description in SQL Server

In SQL Server, details regarding a specific table column – e.g. column name, column id, column data type, column constraints – can be retrieved by joining system tables such as sys.tables, sys.columns, sys.types.

Query 1: Fetching tables and object_id

About sys.tables

sys.tables is a system table and is used for maintaining information on tables in a database. For every table added to the database, a record is created in the sys.tables table. There is only one record for each table and it contains information such as table name, object id of table, created date, modified date, etc. Object ID is unique and we will use it to join this table with other system tables (sys.columns) in order to fetch column details.

The following query can be used to fetch the object_id field of all the tables in a database:

Select name AS TableName, object_id AS ObjectID
From sys.tables
From sys.tables
–– where name = ''

–– Uncomment above line and add

to fetch details for particular table

The result will be look something like this:

TableName

InstrumentAudit
InstrumentMerge
InstrumentMerge_MinDates

ObjectID

375724441
379864420
395864477

Query 2: Fetching Columns

The next step is to fetch columns for these tables. This can be done by joining sys.tables with sys.columns based on object_id for the database tables.

About sys.columns

sys.columns is a system table and is used for maintaining information on columns in a database. For every column added in database, a record is created in the sys.columns table. There is only one record for each column and it contains the following information:

  • Name: The name of the column. This is unique within the table object.
  • Object_id:object_id is unique identifier for table in which the column exists. We will use this column to join sys.columns with sys.tables in order to fetch columns in different tables.
  • Column_id: ID of the column. This is unique within the table object./li>
  • user_type_id: System code for column data type
  • max_length: Maximum length (in bytes) of the column.
  • is_nullable: 1 if Column is nullable.

There are additional columns in this table but for our purposes, the above stated columns will suffice.

The following query can be used to fetch column details (for their corresponding tables) and their data type id by joining sys.columns and sys.tables according to the object_id column:

SELECT TAB.name AS TableName, TAB.object_id AS ObjectID, COL.name AS ColumnName, COL.user_type_id AS DataTypeID
From sys.columns COL
INNER JOIN sys.tables TAB
On COL.object_id = TAB.object_id
-- where TAB.name = ''
-- Uncomment above line and add

to fetch details for particular table
-- where COL.name = ''
-- Uncomment above line and add to fetch details for particular column names

The result should look like this:

 

TableName

InstrumentAudit
InstrumentAudit
InstrumentAudit
InstrumentAudit
InstrumentAudit
InstrumentAudit
InstrumentAudit
InstrumentAudit
InstrumentAudit
InstrumentAudit
InstrumentAudit
InstrumentMerge
InstrumentMerge
InstrumentMerge_MinDates
InstrumentMerge_MinDates
InstrumentMerge_MinDates

ObjectID

375724441
375724441
375724441
375724441
375724441
375724441
375724441
375724441
375724441
375724441
375724441
379864420
379864420
395864477
395864477
395864477

ColumnName

AuditDate
AuditID
ValidFrom
ValidTo
ID
Name
Type
Description
CreateDate
InstrumentOrigin
AuditType
ReutersID
PairBBID
BBID
MinBBDate
ReutersID

DataTypeID

61
56
61
61
56
167
56
167
61
56
175
56
56
56
61
56

Query 3: Add Data Type Name

The next step is to replace Data Type ID with Data Type Name. This can be done by joining the above query with sys.types table.

About sys.types

sys.types is a system table and is used for maintaining information on column data types in a database. This table contains one row for each data type and includes the following information:

  • Name: The name of the column. This is unique within the table object.
  • user_type_id: System code for column data type. . This is unique for this table and is used for joining with sys.columns table.
  • max_length: Maximum length (in bytes) of the column.

As before, there are more columns in this table but for our purposes the above stated columns will suffice.

The following query can be used to replace Data Type ID with Data Type Name by joining sys.types with sys.columns on user_type_id:

SELECT TAB.name AS TableName, TAB.object_id AS ObjectID, COL.name AS ColumnName, TYP.name AS DataTypeName, TYP.max_length AS MaxLength
From sys.columns COL
INNER JOIN sys.tables TAB
On COL.object_id = TAB.object_id
INNER JOIN sys.types TYP
ON TYP.user_type_id = COL.user_type_id

-- where TAB.name = ''
-- Uncomment above line and add

to fetch details for particular table
-- where COL.name = ''
-- Uncomment above line and add to fetch details for particular column names
-- where TYP.name = ''
-- Uncomment above line and add to fetch details for particular Data Type

The result will be the following table, containing the column descriptions we were looking for:

 

TableName

InstrumentAudit
InstrumentAudit
InstrumentAudit
InstrumentAudit
InstrumentAudit
InstrumentAudit
InstrumentAudit
InstrumentAudit
InstrumentAudit
InstrumentAudit
InstrumentAudit
InstrumentMerge
InstrumentMerge
InstrumentMerge_MinDates
InstrumentMerge_MinDates
InstrumentMerge_MinDates

ObjectID

375724441
375724441
375724441
375724441
375724441
375724441
375724441
375724441
375724441
375724441
375724441
379864420
379864420
395864477
395864477
395864477

ColumnName

AuditDate
AuditID
ValidFrom
ValidTo
ID
Name
Type
Description
CreateDate
InstrumentOrigin
AuditType
ReutersID
PairBBID
BBID
MinBBDate
ReutersID

DataTypeName

datetime
Int
datetime
datetime
Int
varchar
Int
varchar
datetime
int
char
int
int
int
datetime
int

MaxLength

8
4
8
8
4
8000
4
8000
8
4
8000
4
4
4
8
4

Gain Deeper Insight Into SQL Server Data

Looking for an SQL reporting tool that enables you to easily perform cross-database joins using data from SQL Server, unstructured data sources, flat files and more? Start your free trial, connect to your SQL server database in just a few clicks, and see powerful business intelligence software in action.

6 crucial steps of preparing data for analysis