SQL Server metadata query with column info

database-metadata-metrics

with schema_info
as (SELECT t.table_catalog
         , t.table_schema
         , t.table_name
         , (
    SELECT
                c.column_name,
                c.ordinal_position,
                CASE
                    WHEN c.DATA_TYPE IN ('varchar', 'char', 'varbinary', 'binary') THEN
                        c.DATA_TYPE + '(' +
                        CASE
                            WHEN c.CHARACTER_MAXIMUM_LENGTH = -1 THEN 'MAX'
                            ELSE CAST(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(5))
                        END + ')'
                    WHEN c.DATA_TYPE IN ('text', 'ntext') THEN
                        'varchar(max)'
                    WHEN c.DATA_TYPE IN ('nvarchar', 'nchar') THEN
                        c.DATA_TYPE + '(' +
                        CASE
                            WHEN c.CHARACTER_MAXIMUM_LENGTH = -1 THEN 'max'
                            ELSE CAST(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(5))
                        END + ')'
                    WHEN c.DATA_TYPE IN ('time', 'datetimeoffset', 'datetime2') THEN
                        c.DATA_TYPE + ISNULL('(' + CAST(c.DATETIME_PRECISION AS VARCHAR(5)) + ')', '')
                    WHEN c.DATA_TYPE IN ('decimal', 'numeric') THEN
                        c.DATA_TYPE + '(' + CAST(c.NUMERIC_PRECISION AS VARCHAR(5)) + ',' + CAST(c.NUMERIC_SCALE AS VARCHAR(5)) + ')'
                    ELSE c.DATA_TYPE
                END AS data_type,
				CASE WHEN c.CHARACTER_MAXIMUM_LENGTH = -1 THEN 'max' ELSE CAST(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(5)) END AS char_length,
                CASE
                    WHEN c.IS_NULLABLE = 'NO' THEN 'not null'
                    ELSE 'null'
                END AS nullability,
                CASE
                    WHEN cu.COLUMN_NAME IS NOT NULL THEN 1
                    ELSE 0
                END AS IsPrimaryKey,
                CAST(1 AS BIT) AS IsIncludedColumn
            FROM INFORMATION_SCHEMA.COLUMNS c
            LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc ON c.TABLE_SCHEMA = tc.TABLE_SCHEMA
                AND c.TABLE_NAME = tc.TABLE_NAME
                AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
            LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu ON tc.TABLE_SCHEMA = cu.TABLE_SCHEMA
                AND tc.TABLE_NAME = cu.TABLE_NAME
                AND tc.CONSTRAINT_NAME = cu.CONSTRAINT_NAME
                AND c.COLUMN_NAME = cu.COLUMN_NAME
            WHERE c.TABLE_NAME = t.TABLE_NAME
            AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
            ORDER BY c.ordinal_position
            FOR JSON AUTO
           ) AS column_metadata
    FROM INFORMATION_SCHEMA.TABLES t
    WHERE t.TABLE_TYPE = 'BASE TABLE'
   )
SELECT @@SERVERNAME                As server_name
     , DB_NAME()                   as [database_name]
     , ts.[schema_name]
     , ts.table_name
     , CAST(null as varchar(1000)) as table_description
     , CAST(null as bit)           as include_table
     , ts.row_count
     , ts.used_space_in_mb
     , ts.data_space_in_mb
     , ts.total_space_in_mb /* 	 , CASE            WHEN LEN(s.column_metadata) > 8000 THEN                NULL            ELSE                s.column_metadata        END                         as column_metadata 	   */
     , s.column_metadata
     , LEN(s.column_metadata)      As column_metadata_length
FROM schema_info s
    LEFT OUTER JOIN
    (
        SELECT object_schema_name(p.object_id)                                     as [schema_name]
             , object_name(p.object_id)                                            as table_name
             , max(p.rows)                                                         AS row_count
             , CAST(ROUND((SUM(a.total_pages) * 8) / 1024.00, 2) as decimal(9, 2)) as total_space_in_mb
             , CAST(ROUND((SUM(a.used_pages) * 8) / 1024.00, 2) as decimal(9, 2))  as used_space_in_mb
             , CAST(ROUND((SUM(a.data_pages) * 8) / 1024.00, 2) as decimal(9, 2))  as data_space_in_mb
        FROM sys.tables                     t
            INNER JOIN sys.indexes          i
                ON t.object_id = i.object_id
            INNER JOIN sys.partitions       p
                ON i.object_id = p.object_id
                   AND i.index_id = p.index_id
            INNER JOIN sys.allocation_units a
                ON p.partition_id = a.container_id
        WHERE i.object_id > 255
              AND i.index_id IN ( 0, 1 )
        GROUP BY object_schema_name(p.object_id)
               , object_name(p.object_id)
    )            ts
        ON (
               ts.schema_name = s.TABLE_SCHEMA
               AND ts.table_name = s.TABLE_NAME
           ) /*     WHERE o.TABLE_SCHEMA IN (#)    AND o.TABLE_NAME IN (*)     		   
WHERE s.table_name IN ( 'Customer'
                      )
					  */
ORDER BY 1
       , 2
       , 3;