Blog

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 = '<TABLENAME>'

–– Uncomment above line and add <Table Name> 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 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.

: 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.

: 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>

: ID of the column. This is unique within the table object./li> user_type_id : System code for column data type

: System code for column data type max_length : Maximum length (in bytes) of the column.

: 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 = '<TABLENAME>'

-- Uncomment above line and add <Table Name> to fetch details for particular table

-- where COL.name = '<COLUMNNAME>'

-- Uncomment above line and add <Column Name> 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

…

See Sisense in action:

Explore Dashboard

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.

: 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.

: 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 = '<TABLENAME>'

-- Uncomment above line and add <Table Name> to fetch details for particular table

-- where COL.name = '<COLUMNNAME>'

-- Uncomment above line and add <Column Name> to fetch details for particular column names

-- where TYP.name = '<DATATYPENAME>'

-- Uncomment above line and add <Data Type Name> 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 a 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.