Create an SQL Server database including all of the Foreign Keys, Unique Keys (which will be the Business Primary Key). If you would like a table description then either you can add it as an extended property of the table in a property called MS_Description, or you can add it in the Excel file
Navigate .rom the mart
Run this SQL and paste it into the Tables tab of the Excel spreadsheet
SELECT DISTINCT t.TABLE_NAME [CODE], t.TABLE_NAME [TITLE], ep.value [DESCRIPTION]
FROM INFORMATION_SCHEMA.TABLES t
JOIN sys.tables st ON t.TABLE_NAME = st.name
LEFT JOIN sys.extended_properties ep ON st.object_id = ep.major_id
AND ep.name = 'MS_Description'
AND ep.minor_id = 0 -- filters on table descriptions
WHERE t.TABLE_NAME NOT LIKE 'sysdiagrams';
Run this SQL and paste it into the Fields tab of the Excel spreadsheet
WITH FKs AS (
SELECT KCU1.CONSTRAINT_CATALOG AS CATALOG_NAME
,KCU1.CONSTRAINT_SCHEMA AS FK_SCHEMA_NAME
,KCU1.CONSTRAINT_NAME AS FK_CONSTRAINT_NAME
,KCU1.TABLE_NAME AS FK_TABLE_NAME
,KCU1.COLUMN_NAME AS FK_COLUMN_NAME
,KCU2.CONSTRAINT_SCHEMA AS REFERENCED_SCHEMA_NAME
,KCU2.CONSTRAINT_NAME AS REFERENCED_CONSTRAINT_NAME
,KCU2.TABLE_NAME AS REFERENCED_TABLE_NAME
,KCU2.COLUMN_NAME AS REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU1
ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG
AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU2
ON KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG
AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA
AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME
AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION
-- Exclude inter-database foreign keys
WHERE KCU1.CONSTRAINT_CATALOG = KCU2.CONSTRAINT_CATALOG
),PKs AS (
SELECT tc.TABLE_CATALOG, tc.TABLE_SCHEMA, tc.TABLE_NAME, ccu.COLUMN_NAME ,tc.CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
ON ccu.TABLE_CATALOG = tc.TABLE_CATALOG
AND ccu.TABLE_SCHEMA = tc.TABLE_SCHEMA
AND ccu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
)
SELECT DISTINCT
st.name AS [TABLE_CODE], c.COLUMN_NAME [CODE], c.COLUMN_NAME [TITLE]
,ISNULL(ep.value, '') [DESCRIPTION]
, CASE
WHEN FKs.REFERENCED_TABLE_NAME IS NOT NULL THEN 'FOREIGN_KEY'
WHEN c.DATA_TYPE = 'bigint' THEN 'BIG_INTEGER'
WHEN c.DATA_TYPE = 'uniqueidentifier' THEN 'GUID'
WHEN c.DATA_TYPE = 'bit' THEN 'BOOLEAN'
WHEN c.DATA_TYPE IN ('date', 'datetime2') THEN 'DATE'
WHEN c.DATA_TYPE IN ('datetime', 'datetime2', 'smalldatetime', 'time') THEN 'DATE_TIME'
WHEN c.DATA_TYPE IN ('decimal', 'float', 'numeric', 'real') THEN
CASE
WHEN c.NUMERIC_SCALE > 9 THEN 'DECIMAL_28_14'
WHEN c.NUMERIC_PRECISION > 16 THEN 'DECIMAL_28_9'
ELSE 'DECIMAL_16_9'
END
WHEN c.DATA_TYPE IN ('int', 'smallint', 'tinyint') THEN 'INTEGER'
WHEN c.DATA_TYPE IN ('binary', 'varbinary', 'image') THEN 'MEDIA'
WHEN c.DATA_TYPE IN ('money', 'smallmoney') THEN 'DECIMAL_16_9'
WHEN c.DATA_TYPE IN ('varchar', 'nvarchar', 'char', 'nchar', 'sysname') THEN
CASE
WHEN CHARACTER_MAXIMUM_LENGTH <= 2 THEN 'TEXT_2'
WHEN CHARACTER_MAXIMUM_LENGTH = 3 THEN 'TEXT_3'
WHEN CHARACTER_MAXIMUM_LENGTH <= 10 THEN 'TEXT_10'
WHEN CHARACTER_MAXIMUM_LENGTH <= 50 THEN 'TEXT_50'
WHEN CHARACTER_MAXIMUM_LENGTH <= 450 THEN 'TEXT_450'
WHEN CHARACTER_MAXIMUM_LENGTH <= 4000 THEN 'TEXT_4000'
ELSE 'TEXT_MAX'
END
WHEN c.DATA_TYPE IN ('text', 'ntext') THEN 'TEXT_MAX'
WHEN c.DATA_TYPE = 'xml' THEN 'XML'
ELSE ''
END AS [FIELD_TYPE_CODE]
,ISNULL(FKs.REFERENCED_TABLE_NAME, '') [FK_TABLE_CODE]
,CASE WHEN c.IS_NULLABLE LIKE 'NO' THEN 1 ELSE 0 END [IS_REQUIRED]
,CASE WHEN PKs.COLUMN_NAME IS NULL THEN 0 ELSE 1 END IS_PRIMARY_KEY
,'' AS USED_TO_LABEL
,(c.ORDINAL_POSITION * 10) [SEQUENCE]
FROM INFORMATION_SCHEMA.COLUMNS c
JOIN sys.tables st ON c.TABLE_NAME = st.name
JOIN sys.columns sc ON c.COLUMN_NAME = sc.name
-- Foreign keys
LEFT JOIN FKs
ON c.TABLE_SCHEMA = FKs.FK_SCHEMA_NAME
AND c.TABLE_NAME = FKs.FK_TABLE_NAME AND c.COLUMN_NAME = Fks.FK_COLUMN_NAME
-- Primary keys
LEFT JOIN PKs
ON c.TABLE_SCHEMA = PKs.TABLE_SCHEMA
AND c.TABLE_NAME = PKs.TABLE_NAME AND c.COLUMN_NAME = Pks.COLUMN_NAME
-- Description
LEFT JOIN sys.extended_properties ep
ON st.object_id = ep.major_id AND sc.column_id = ep.minor_id
AND ep.name = 'MS_Description'
WHERE c.TABLE_NAME NOT LIKE 'sysdiagrams'
ORDER BY TABLE_CODE, SEQUENCE
When the data has been inserted into the spreadsheet, upload it into the Mart