Get column metadata

WITH cols AS(
  SELECT
    OBJECT_SCHEMA_NAME(o.[object_id]) AS TableSchema
  , o.[name] AS TableName
  , c.[name] AS ColumnName
  , c.column_id AS ColumnOrdinal
  , t.[name] AS TypeName
  , c.max_length AS [MaxLength]
  , c.scale
  , c.[precision]
  , c.is_nullable
  , COALESCE(ic.index_column_id, 0) AS PkOrdinal
  , UPPER(t.[name]) 
      + COALESCE('(' + CAST(
          CASE 
            WHEN t.[name] LIKE '%char' AND c.max_length = -1 THEN 'MAX'
            WHEN t.[name] LIKE 'n%char' THEN CAST(c.max_length/2 AS VARCHAR)
            WHEN t.[name] LIKE '%char' THEN CAST(c.max_length AS VARCHAR)
          END AS VARCHAR) + ')', '') AS SqlType
  , IIF(hc.[description] LIKE '%case-sensitive%', 1, 0) AS IsCaseSensitive
  FROM sys.objects o
    INNER JOIN sys.columns c ON c.[object_id] = o.[object_id]
    INNER JOIN sys.types t 
      ON t.system_type_id = c.system_type_id
      AND t.system_type_id = t.user_type_id
    LEFT JOIN sys.fn_helpcollations() hc ON hc.[name] = c.collation_name
    LEFT JOIN sys.indexes i 
      ON i.[object_id] = o.[object_id]
      AND i.is_primary_key = 1
    LEFT JOIN sys.index_columns ic
      ON ic.[object_id] = i.[object_id]
      AND ic.index_id = i.index_id
      AND ic.column_id = c.column_id
)
SELECT *
FROM cols