Self documenting database (SQL Server)

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;