Here is a query:
SELECT s.name AS "Schema Name", t.name AS "Table Name", c.name AS "Column Name",
ty.name AS "Data type",
CASE WHEN ty.name LIKE '%char' OR ty.name LIKE '%binary' THEN
CASE WHEN c.max_length = -1 THEN 'MAX'
WHEN ty.name LIKE 'n%char' THEN convert(varchar(10), c.max_length / 2)
ELSE convert(varchar(10), c.max_length / 2)
END
END AS "Max length",
CASE WHEN ty.name IN ('decimal', 'numeric') THEN c.precision END AS Precision,
CASE WHEN ty.name IN ('decimal', 'numeric', 'time', 'datetime2', 'datetimeoffset')
THEN c.scale
END AS Scale
FROM sys.tables t
JOIN sys.schemas s ON s.schema_id = t.schema_id
JOIN sys.columns c ON t.object_id = c.object_id
JOIN sys.types ty ON ty.system_type_id = c.system_type_id
WHERE ty.user_type_id <= 255
ORDER BY s.name, t.name, c.column_id
To get the result into Excel, you can simply copy and paste.
You can also go to the Data tab in the Ribbon and select Get Data and in this menu pick SQL Server database. Select Advanced to be able to use a query. Using this method may seem a little more cumbersome, but when you copy and paste, Excel may be too smart for its own good, and accidents can happen. When you run the query from Excel, Excel gets information about the data types, and does have to be smart.