Read Microsoft SQL Server Schema Information using T-SQL

TableColumnAnalysis.sql

SELECT t.[name] AS [Table],
       c.[name] AS [Column],
       ex1.[value] AS [Description],
       CASE
         WHEN ty.[name] IN ('geometry', 'geography') THEN ty.[name]
         WHEN ty.[name] IN ('decimal', 'numeric') THEN ty.[name] + '(' + CAST(c.[precision] AS VARCHAR(10)) + ', ' + CAST(c.[scale] AS VARCHAR(10)) + ')'
         WHEN ty.[max_length] = c.[max_length] THEN ty.[name]
         WHEN c.[max_length] = -1 THEN ty.[name] + '(MAX)'
         ELSE ty.[name] + '(' + CAST(c.[max_length] AS VARCHAR(10)) + ')'
       END AS [Type],
       c.[is_nullable] AS [IsNullable],
       c.[is_identity] AS [IsIdentity],
       CASE
         WHEN ex2.[value] IS NULL THEN 0
         ELSE 1
       END AS [IsSystemTable]
FROM sys.columns c
INNER JOIN sys.tables t ON c.object_id = t.object_id
INNER JOIN sys.types ty ON ty.system_type_id = c.system_type_id AND ty.[name] NOT IN ('sysname')
OUTER APPLY fn_listextendedproperty('Description', 'schema', 'dbo', 'table', t.name, 'column', c.name) ex1
OUTER APPLY fn_listextendedproperty('microsoft_database_tools_support', 'schema', 'dbo', 'table', t.name, NULL, NULL) ex2
ORDER BY t.[object_id], c.[column_id]