List the Column Sizes of Each Table in a SQL Server Database


SELECT as ColumnName
    ,'.' AS ObjectName
    ,o.type_desc AS ObjectType
          WHEN IN ('char','varchar') THEN'('+CASE WHEN s.max_length<0 then 'MAX' ELSE CONVERT(varchar(10),s.max_length) END+')'
          WHEN IN ('nvarchar','nchar') THEN'('+CASE WHEN s.max_length<0 then 'MAX' ELSE CONVERT(varchar(10),s.max_length/2) END+')'
        WHEN IN ('numeric') THEN'('+CONVERT(varchar(10),s.precision)+','+CONVERT(varchar(10),s.scale)+')'
      END AS DataType

          WHEN s.is_nullable=1 THEN 'NULL'
        ELSE 'NOT NULL'
    END AS Nullable
          WHEN ic.column_id IS NULL THEN ''
          ELSE ' identity('+ISNULL(CONVERT(varchar(10),ic.seed_value),'')+','+ISNULL(CONVERT(varchar(10),ic.increment_value),'')+')='+ISNULL(CONVERT(varchar(10),ic.last_value),'null')
          WHEN sc.column_id IS NULL THEN ''
          ELSE ' computed('+ISNULL(sc.definition,'')+')'
          WHEN cc.object_id IS NULL THEN ''
          ELSE ' check('+ISNULL(cc.definition,'')+')'
        AS MiscInfo
FROM sys.columns                           s
    INNER JOIN sys.types                   t ON s.system_type_id=t.user_type_id and t.is_user_defined=0
    INNER JOIN sys.objects                 o ON s.object_id=o.object_id
    INNER JOIN sys.schemas                sh on o.schema_id=sh.schema_id
    LEFT OUTER JOIN sys.identity_columns  ic ON s.object_id=ic.object_id AND s.column_id=ic.column_id
    LEFT OUTER JOIN sys.computed_columns  sc ON s.object_id=sc.object_id AND s.column_id=sc.column_id
    LEFT OUTER JOIN sys.check_constraints cc ON s.object_id=cc.parent_object_id AND s.column_id=cc.parent_column_id
WHERE o.type_desc IN ('USER_TABLE', 'view')
ORDER BY CASE WHEN s.max_length<0 THEN 0 ELSE 1 END, s.max_length desc