-- query to return every table and column in order

select isc.TABLE_CATALOG
,isc.TABLE_SCHEMA
,isc.TABLE_NAME
,max(SI.rows) as RecCount
,isc.COLUMN_NAME
,isc.ORDINAL_POSITION
,isc.COLUMN_DEFAULT
,isc.IS_NULLABLE
,isc.DATA_TYPE
,isc.CHARACTER_MAXIMUM_LENGTH
,isc.NUMERIC_PRECISION
,isc.NUMERIC_SCALE
,isc.DATETIME_PRECISION
,isc.DOMAIN_NAME
from INFORMATION_SCHEMA.COLUMNS isc
join INFORMATION_SCHEMA.TABLES ist
on isc.TABLE_NAME = ist.TABLE_NAME
join SYSINDEXES SI
on ist.TABLE_NAME = OBJECT_NAME(SI.ID)
where isc.TABLE_CATALOG = 'xxxx'
and isc.TABLE_SCHEMA = 'dbo'
and ist.TABLE_TYPE = 'Base Table'
group by
isc.TABLE_CATALOG
,isc.TABLE_SCHEMA
,isc.TABLE_NAME
,isc.COLUMN_NAME
,isc.ORDINAL_POSITION
,isc.COLUMN_DEFAULT
,isc.IS_NULLABLE
,isc.DATA_TYPE
,isc.CHARACTER_MAXIMUM_LENGTH
,isc.NUMERIC_PRECISION
,isc.NUMERIC_SCALE
,isc.DATETIME_PRECISION
,isc.DOMAIN_NAME
order by TABLE_NAME, ORDINAL_POSITION

-- query to determine to find all the keys in the database, which columns are involved, and report if they are combined keys (ColumnCount)


;with CTE_SingleKey as 
(select
'Single Column Key' as KeyType
,COUNT(scu.column_name) as ColumnCount
,scu.CONSTRAINT_NAME
,kc.type
,kc.type_desc
,scu.TABLE_NAME
from sys.key_constraints kc
join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE scu
on kc.name = scu.CONSTRAINT_NAME
group by
scu.CONSTRAINT_NAME
,kc.type
,kc.type_desc
,scu.TABLE_NAME
having COUNT(scu.column_name)
)
,CTE_MultiKey as 
(select
'Multi Column Key' as KeyType
,COUNT(scu.column_name) as ColumnsCount
,scu.CONSTRAINT_NAME
,kc.type
,kc.type_desc
,scu.TABLE_NAME
from sys.key_constraints kc
join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE scu
on kc.name = scu.CONSTRAINT_NAME
group by
scu.CONSTRAINT_NAME
,kc.type
,kc.type_desc
,scu.TABLE_NAME
having COUNT(scu.column_name) > 1
)

select mkey.*
,scu.COLUMN_NAME
from CTE_MultiKey mkey
join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE scu
on mkey.CONSTRAINT_NAME = scu.CONSTRAINT_NAME

union

select skey.*
,scu.COLUMN_NAME
from CTE_SingleKey skey
join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE scu
on skey.CONSTRAINT_NAME = scu.CONSTRAINT_NAME
order by
TABLE_NAME
,CONSTRAINT_NAME
,COLUMN_NAME

-- query to find all foreign keys and referenced tables/columns

SELECT obj.name AS FK_NAME,
schem.name AS [schema_name],
Table1.name AS [table],
Columns1.name AS [column],
table2.name AS [referenced_table],
Columns2.name AS [referenced_column]
FROM sys.foreign_key_columns fkCol
INNER JOIN sys.objects obj
ON obj.object_id = fkCol.constraint_object_id
INNER JOIN sys.tables Table1
ON Table1.object_id = fkCol.parent_object_id
INNER JOIN sys.schemas schem
ON Table1.schema_id = schem.schema_id
INNER JOIN sys.columns Columns1
ON Columns1.column_id = parent_column_id AND Columns1.object_id = Table1.object_id
INNER JOIN sys.tables table2
ON table2.object_id = fkCol.referenced_object_id
INNER JOIN sys.columns Columns2
ON Columns2.column_id = referenced_column_id
AND Columns2.object_id = table2.object_id