SQL cheat sheet: Retrieving column description in SQL server

• About sys.tables
• About sys.columns
• About sys.types

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, and 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 tableThe result will be look something like this:

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 a 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 the 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 namesThe result should look like this:

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 TypeThe result will be the following table, containing the column descriptions we were looking for: