SELECT [s].[name] [SchemaName], [t].[name] [TableName], [c].[name] [ColumnName], [tp].[name],
CASE
WHEN [tp].[name] IN ('bigint', 'int', 'smallint', 'tinyint', 'real', 'float',
'bit', 'date', 'datetime', 'geography', 'geometry', 'hierarchyid',
'image', 'smallmoney', 'money', 'ntext', 'text', 'timestamp', 'smalldatetime',
'sql_variant', 'sysname', 'uniqueidentifier', 'xml')
THEN [tp].[name]
WHEN [tp].[name] IN ('nchar', 'nvarchar', 'varbinary', 'varchar' ) AND [c].[max_length] = -1
THEN [tp].[name] + '(max)'
WHEN [tp].[name] IN ('nchar', 'nvarchar') AND [c].[max_length] <> -1
THEN [tp].[name] + '(' + CAST([c].[max_length]/2 AS VARCHAR(10)) + ')'
WHEN [tp].[name] IN ('binary', 'char', 'varbinary', 'varchar' ) AND [c].[max_length] <> -1
THEN [tp].[name] + '(' + CAST([c].[max_length] AS VARCHAR(10)) + ')'
WHEN [tp].[name] IN ( 'decimal', 'numeric')
THEN [tp].[name] + '(' + CAST([c].[precision] AS VARCHAR(10)) + ', ' + CAST([c].[scale] AS VARCHAR(10)) + ')'
WHEN [tp].[name] IN ('datetime2', 'datetimeoffset', 'time' )
THEN [tp].[name] + '(' + CAST([c].[scale] AS VARCHAR(10)) + ')'
WHEN [tp].[name] IN ('datetime2', 'datetimeoffset', 'time' )
THEN [tp].[name] + '(' + CAST([c].[scale] AS VARCHAR(10)) + ')'
ELSE [tp].[name]
END, [c].[scale], [c].[precision], [c].[max_length]
FROM [sys].[tables] [t]
INNER JOIN [sys].[columns] [c] ON [c].[object_id] = [t].[object_id]
INNER JOIN [sys].[schemas] [s] ON [s].[schema_id] = [t].[schema_id]
INNER JOIN [sys]. [tp] ON [tp].[system_type_id] = [c].[system_type_id] AND [tp].[user_type_id] = [c].[user_type_id]
ORDER BY [s].[name], [t].[name], [c].[name]
