SQL Server: An Interesting way to get column related info – Part 2

Hi Friends,

Yesterday I published one article “How to find column related detailed info for any table?” In that article i talked about an interesting way to get column related info, but in that query you were not able to get Data-type related info. The View gives you only data_type ID. Now there is another view which has all info about all available datatypes in SQL Server.

By this time you must have realized how we will get this info, yes, we will join both these tables to get the info in one shot.

Use the below mentioned query to the desired output.

select object_id, c.name, c.system_type_id, c.max_length, 
c.is_nullable,c.is_identity, c.user_type_id, t.name
from sys.columns c join sys.types t
on c.user_type_id = t.user_type_id
where object_id = object_id('test2')

This is how it shows the output.


1_SQL_Server_An_Interesting_way_to_get_column_related_Part2 info



Sarabpreet Anand

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook


One Comment on “SQL Server: An Interesting way to get column related info – Part 2”

  1. we can also use the inbuilt function “Type_Name” to get the same.

    select object_id, name, system_type_id, max_length,
    is_nullable,is_identity, user_type_id, TYPE_NAME(user_type_id) as DataType
    from sys.columns
    where object_id = object_id(‘Test2’)

Leave a Reply

Your email address will not be published.