Like most developers (and in fact most sane people) I dont like doing documentation, however you can’t deny the value of it so I went about trying to see how it could be done in SQL Server without having to buy some software or have a prolonged fight with MS Word!
A quick google led me to the extended properties page of a columns properties (in management studio)
You can add all the extra meta data you require here specific to your project, in this example I’ve added 3 extended properties – Source, Frequency loaded and Owner. I’ve then given these properties values for this particular column.
That meta data is then stored against the column’s id in a system table called sys.extended_properties, we can see this by running a quick select statement:
Now all we need to do is pivot this so we get one row with all the extended properties as columns and we can join onto the 
INFORMATION_SCHEMA.COLUMNS system view using the major and minor ids. I have had to do a bit of jiggery pokery in order to not have to specify the names of the extended properties (we wont necessarily know what they are all the time!). I dont have time to go into it in detail but have a look at the comments in the code below and it should hopefully make sense.
declare
@names asvarchar(100)
declare
@sql asnvarchar(max)
— populate @names variable with the columns we want to include in our PIVOT statement
select
@names =coalesce(@names +‘, ‘,”)+ name from
sys.extended_properties ep
— build up the sql for the PIVOT statement (including the columns names captured above)
set
@sql =
‘SELECT major_id, minor_id,‘
+ @names +‘into ##tmp
FROM
(SELECT name, value, major_id, minor_id
FROM sys.extended_properties
WHERE class = 1) AS SourceTable
PIVOT
(
max(Value)
FOR name IN (‘
+ @names +‘)) AS PivotTable’
— run the sql to save this data in ##tmp
exec
sp_executesql@sql
— Bring it all together by pulling the columns details from information_schema
— and then joining on the extra metadata gathered above
SELECT
TABLE_NAME
, COLUMN_NAME, COLUMN_DEFAULT,c
.IS_NULLABLE,case
whencast(DATA_TYPE ASvarchar(10))like‘%char%’
thencast(DATA_TYPE ASvarchar(10))+‘(‘+replace(cast(CHARACTER_MAXIMUM_LENGTH ASvarchar(6)),‘-1’,‘max’)+‘)’
else data_type end,ep
.*FROM
INFORMATION_SCHEMA
.COLUMNS cINNER
JOIN
sys
.columns scON
OBJECT_ID
(c.TABLE_SCHEMA +‘.’+ c.TABLE_NAME)= sc.[object_id]
AND
c.COLUMN_NAME = sc.name
LEFT
OUTERJOIN
##tmp ep
ON
sc
.[object_id] = ep.major_idAND
sc.[column_id] = ep.minor_id
ORDER
BY
1
, 2;
Dan's BI Braindump 