<ul class="anchorlinks wp-block-list"><li><a href="#tables">About sys.tables</a></li><li><a href="#columns">About sys.columns</a></li><li><a href="#types">About sys.types</a></li></ul>



<p>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.</p>



<h3 class="wp-block-heading">Query 1: Fetching tables and object_id</h3>



<h2 class="wp-block-heading" id="tables">About sys.tables</h2>



<p>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.</p>



<p><strong>SQL Analytics Starter Kit: Best Practices, Tips, and Tricks:</strong></p>



<a class="action-btn " href="https://www.sisense.com/whitepapers/sql-analytics-best-practices-tips-and-tricks/" target="_blank" rel="noopener noreferrer">Get the Starter Kit</a>



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



<p><code>Select name AS TableName, object_id AS ObjectID<br>
From sys.tables<br>	 	From sys.tables<br>
–– where name = '&lt;TABLENAME&gt;'<br>	 	
–– Uncomment above line and add &lt;Table Name&gt; to fetch details for particular table</code></p>



<p>The result will be look something like this:</p>



<div class="row table notranslate" style="font-size: 16px;">
<div class="col-xs-12 col-sm-3">
<h3 style="font-size: 16px;font-weight: bold;margin-bottom: 0;line-height: 30px;">TableName</h3>
<p>InstrumentAudit<br>
InstrumentMerge<br>
InstrumentMerge_MinDates</p>
</div>
<p>
</p><div class="col-xs-12 col-sm-3 col-sm-offset-1">
<h3 style="font-size: 16px;font-weight: bold;margin-bottom: 0;line-height: 30px">ObjectID</h3>
<p>375724441<br>
379864420<br>
395864477</p>
</div>
<p></p>
</div>



<h3 class="wp-block-heading">Query 2: Fetching Columns</h3>



<p>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.</p>



<h2 class="wp-block-heading" id="columns">About sys.columns</h2>



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



<ul class="wp-block-list"><li><b>Name</b>: The name of the column. This is unique within the table object.</li><li><b>Object_id</b>: 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.</li><li><b>Column_id</b>: ID of the column. This is unique within the table object./li&gt;</li><li><b>user_type_id</b>: System code for column data type</li><li><b>max_length</b>: Maximum length (in bytes) of the column.</li><li><b>is_nullable</b>: 1 if column is nullable.</li></ul>



<p>There are additional columns in this table, but for our purposes, the above-stated columns will suffice.</p>



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



<p><code>SELECT TAB.name AS TableName, TAB.object_id AS ObjectID, COL.name AS ColumnName, COL.user_type_id AS DataTypeID<br>
From sys.columns COL<br>
INNER JOIN sys.tables TAB<br>
On COL.object_id = TAB.object_id<br>
-- where TAB.name = '&lt;TABLENAME&gt;'<br>
-- Uncomment above line and add &lt;Table Name&gt; to fetch details for particular table<br>
-- where COL.name = '&lt;COLUMNNAME&gt;'<br>
-- Uncomment above line and add &lt;Column Name&gt; to fetch details for particular column names<br>
</code></p>



<p>The result should look like this:</p>



<div class="row table notranslate" style="font-size: 16px;">
<div class="col-xs-12 col-sm-4">
<h3 style="font-size: 16px;font-weight: bold;margin-bottom: 0;line-height: 30px;">TableName</h3>
<p>InstrumentAudit<br>
InstrumentAudit<br>
InstrumentAudit<br>
InstrumentAudit<br>
InstrumentAudit<br>
InstrumentAudit<br>
InstrumentAudit<br>
InstrumentAudit<br>
InstrumentAudit<br>
InstrumentAudit<br>
InstrumentAudit<br>
InstrumentMerge<br>
InstrumentMerge<br>
InstrumentMerge_MinDates<br>
InstrumentMerge_MinDates<br>
InstrumentMerge_MinDates<br>
…</p>
</div>
<p>
</p><div class="col-xs-12 col-sm-2">
<h3 style="font-size: 16px;font-weight: bold;margin-bottom: 0;line-height: 30px;">ObjectID</h3>
<p>375724441<br>
375724441<br>
375724441<br>
375724441<br>
375724441<br>
375724441<br>
375724441<br>
375724441<br>
375724441<br>
375724441<br>
375724441<br>
379864420<br>
379864420<br>
395864477<br>
395864477<br>
395864477<br>
…</p>
</div>
<p>
</p><div class="col-xs-12 col-sm-2">
<h3 style="font-size: 16px;font-weight: bold;margin-bottom: 0;line-height: 30px;">ColumnName</h3>
<p>AuditDate<br>
AuditID<br>
ValidFrom<br>
ValidTo<br>
ID<br>
Name<br>
Type<br>
Description<br>
CreateDate<br>
InstrumentOrigin<br>
AuditType<br>
ReutersID<br>
PairBBID<br>
BBID<br>
MinBBDate<br>
ReutersID<br>
…</p>
</div>
<p>
</p><div class="col-xs-12 col-sm-2">
<h3 style="font-size: 16px;font-weight: bold;margin-bottom: 0;line-height: 30px;">DataTypeID</h3>
<p>61<br>
56<br>
61<br>
61<br>
56<br>
167<br>
56<br>
167<br>
61<br>
56<br>
175<br>
56<br>
56<br>
56<br>
61<br>
56<br>
…</p>
</div>
<p></p>
</div>



<p><strong>See Sisense in action:</strong></p>


<p>[sisense-dashboard-example name=&#8221;quality-assurance-project-status&#8221;]</p>



<h3 class="wp-block-heading">Query 3: Add Data Type Name</h3>



<p>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.</p>



<h2 class="wp-block-heading" id="types">About sys.types</h2>



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



<ul class="wp-block-list"><li><b>Name</b>: The name of the column. This is unique within the table object.</li><li><b>user_type_id</b>: System code for column data type. This is unique for this table and is used for joining with sys.columns table.</li><li><b>max_length</b>: Maximum length (in bytes) of the column.</li></ul>



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



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



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



<p><code>-- where TAB.name = '&lt;TABLENAME&gt;'<br>
-- Uncomment above line and add &lt;Table Name&gt; to fetch details for particular table<br>
-- where COL.name = '&lt;COLUMNNAME&gt;'<br>
-- Uncomment above line and add &lt;Column Name&gt; to fetch details for particular column names<br>
-- where TYP.name = '&lt;DATATYPENAME&gt;'<br>
-- Uncomment above line and add &lt;Data Type Name&gt; to fetch details for particular Data Type<br>
</code></p>



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



<div class="row table notranslate" style="font-size: 16px;">
<div class="col-xs-12 col-sm-4">
<h3 style="font-size: 16px;font-weight: bold;margin-bottom: 0;line-height: 30px">TableName</h3>
<p>InstrumentAudit<br>
InstrumentAudit<br>
InstrumentAudit<br>
InstrumentAudit<br>
InstrumentAudit<br>
InstrumentAudit<br>
InstrumentAudit<br>
InstrumentAudit<br>
InstrumentAudit<br>
InstrumentAudit<br>
InstrumentAudit<br>
InstrumentMerge<br>
InstrumentMerge<br>
InstrumentMerge_MinDates<br>
InstrumentMerge_MinDates<br>
InstrumentMerge_MinDates<br>
…</p>
</div>
<p>
</p><div class="col-xs-12 col-sm-2">
<h3 style="font-size: 16px;font-weight: bold;margin-bottom: 0;line-height: 30px;">ObjectID</h3>
<p>375724441<br>
375724441<br>
375724441<br>
375724441<br>
375724441<br>
375724441<br>
375724441<br>
375724441<br>
375724441<br>
375724441<br>
375724441<br>
379864420<br>
379864420<br>
395864477<br>
395864477<br>
395864477<br>
…</p>
</div>
<p>
</p><div class="col-xs-12 col-sm-2">
<h3 style="font-size: 16px;font-weight: bold;margin-bottom: 0;line-height: 30px;">ColumnName</h3>
<p>AuditDate<br>
AuditID<br>
ValidFrom<br>
ValidTo<br>
ID<br>
Name<br>
Type<br>
Description<br>
CreateDate<br>
InstrumentOrigin<br>
AuditType<br>
ReutersID<br>
PairBBID<br>
BBID<br>
MinBBDate<br>
ReutersID<br>
…</p>
</div>
<p>
</p><div class="col-xs-12 col-sm-2">
<h3 style="font-size: 16px;font-weight: bold;margin-bottom: 0;line-height: 30px;">DataTypeName</h3>
<p>datetime<br>
Int<br>
datetime<br>
datetime<br>
Int<br>
varchar<br>
Int<br>
varchar<br>
datetime<br>
int<br>
char<br>
int<br>
int<br>
int<br>
datetime<br>
int<br>
…</p>
</div>
<p>
</p><div class="col-xs-12 col-sm-2">
<h3 style="font-size: 16px;font-weight: bold;margin-bottom: 0;line-height: 30px;">MaxLength</h3>
<p>8<br>
4<br>
8<br>
8<br>
4<br>
8000<br>
4<br>
8000<br>
8<br>
4<br>
8000<br>
4<br>
4<br>
4<br>
8<br>
4<br>
…</p>
</div>
<p></p>
</div>



<h2 class="wp-block-heading">Gain Deeper Insight Into SQL Server Data</h2>



<p>Looking for a <a href="/reporting/">SQL reporting tool</a> that enables you to easily perform cross-database joins using data from <a href="https://www.sisense.com/data-connectors/sql-server/">SQL Server</a>, unstructured data sources, flat files and more? <a href="/get/free-trial/">Start your free trial</a>, connect to your SQL server database in just a few clicks, and see powerful <a href="https://www.sisense.com/product/business-teams/">business intelligence software</a> in action.</p>



<p><strong>SQL Analytics Starter Kit: Best Practices, Tips, and Tricks:</strong></p>



<a class="action-btn " href="https://www.sisense.com/whitepapers/sql-analytics-best-practices-tips-and-tricks/" target="_blank" rel="noopener noreferrer">Get the Starter Kit</a>

SQL Cheat Sheet: Retrieving Column Description in SQL Server

SQL Superstar

LinkedIn

Twitter

GitHub

curve-image-unique-image-unique

curve

3-dark-2-image-unique-image-unique

3 DARK 2

Get the latest in analytics right in your inbox.

Article