-- List all columns 
select schema_name(tab.schema_id)+'.'+tab.name as [name],
    col.name as column_name, 
	CASE
		WHEN t.name='nvarchar' THEN t.name+'('+CAST(col.max_length AS varchar)+')'
		ELSE t.name
	END AS data_type
from sys.tables as tab
    inner join sys.columns as col
        on tab.object_id = col.object_id
    left join sys.types as t
    on col.user_type_id = t.user_type_id
order by [name]
FOR JSON AUTO


-- List count of each table
SELECT
      SCHEMA_NAME(sOBJ.schema_id) + '.' + sOBJ.name AS [name]
      , SUM(sPTN.Rows) AS row_count
FROM 
      sys.objects AS sOBJ
      INNER JOIN sys.partitions AS sPTN
            ON sOBJ.object_id = sPTN.object_id
WHERE
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
      AND index_id < 2 -- 0:Heap, 1:Clustered
GROUP BY 
      sOBJ.schema_id
      , sOBJ.name
ORDER BY [name]
FOR JSON AUTO

-- List all foreign key relations
select schema_name(fk_tab.schema_id) + '.' + fk_tab.name as target,
    schema_name(pk_tab.schema_id) + '.' + pk_tab.name as source,
    substring(column_names, 1, len(column_names)-1) as [fk_columns],
    fk.name as fk_constraint_name
from sys.foreign_keys fk
    inner join sys.tables fk_tab
        on fk_tab.object_id = fk.parent_object_id
    inner join sys.tables pk_tab
        on pk_tab.object_id = fk.referenced_object_id
    cross apply (select col.[name] + ', '
                    from sys.foreign_key_columns fk_c
                        inner join sys.columns col
                            on fk_c.parent_object_id = col.object_id
                            and fk_c.parent_column_id = col.column_id
                    where fk_c.parent_object_id = fk_tab.object_id
                      and fk_c.constraint_object_id = fk.object_id
                            order by col.column_id
                            for xml path ('') ) D (column_names)
order by schema_name(fk_tab.schema_id) + '.' + fk_tab.name,
    schema_name(pk_tab.schema_id) + '.' + pk_tab.name
FOR JSON AUTO