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.
sys.types.
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.
Regards
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
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’)