Thought I would share a quick post about retrieving all the data types of all columns in a database. This can be quite a handy script when you are comparing development and production databases to make sure all the data types match in each table.

The script displays the order in which the columns appear in the table, the schema the column in the table belongs to, whether is is null or not, the maximum length and the precision of the column.

SELECT  [Schema]			= SCH.name 
       ,TableName			= TBS.name
       ,column_name			= COL.name 
       ,COL.column_id
       ,DatatypeSchema		= SCHEMA_NAME(TYP.[schema_id])
       ,Datatypename		= TYP.name
       ,TYP.is_user_defined
       ,TYP.is_assembly_type
       ,COL.is_nullable
       ,COL.max_length
       ,COL.[Precision]
       ,COL.scale
FROM sys.columns    COL
JOIN sys.types      TYP ON COL.user_type_id   = TYP.user_type_id
JOIN sys.tables     TBS ON TBS.[object_id]    = COL.[object_id]
JOIN sys.schemas    SCH ON SCH.[schema_id]    = TBS.[schema_id]
ORDER BY SCH.name, TBS.name, COL.column_id

Hope this can be of great use to you!
StayYoungKeepLearning

Leave a Reply