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.