System Documentation: My Method
Jorge Segarra (Blog | Twitter) posed the question System Documentation: What’s your method?. In my experience documentation has either been nonexistent, out of date or even worse, plain wrong. These situations often get blamed on lack of time dedicated to documentation tasks.
Therefore I’ve always aimed at making documentation easy or even fun. I’ve blogged before about auditing SQL Servers with Powershell and auditing network adapters with Powershell. I’m very much a proponent of using Powershell to make documentation easy and fun. Of course, it can’t help with everything, so this should only form part of the overall strategy.
I’m also in favour of Documenting Databases in the sense of actually adding comments, in the form of extended properties, to the database itself. This seems to be a no-brainer to me. Keep it inside the database and generate your documentation from this. I recently produced a few views to pull together this information that can be easily exported into a spreadsheet.
Firstly I created a schema to group these views together. This makes their function clear.
CREATE SCHEMA Documentation; GO |
Then I placed a comment on this new schema.
EXEC sys.sp_addextendedproperty @name=N'Description', @value=N'This schema contains views that exposes extended properties for database tables, column, view and other objects.' , @level0type=N'SCHEMA',@level0name=N'Documentation' GO |
Then I created these views based on SQL Server’s system views.
CREATE VIEW Documentation.ColumnComments AS -- Column Comments SELECT sch.name AS SchemaName, tabs.name AS TableName, cols.name AS ColumnName, ty.name AS DataType, cols.max_length AS MaxLength, ep.name AS PropertyName, ep.value AS PropertyValue FROM sys.columns cols INNER JOIN sys.tables tabs ON cols.object_id = tabs.object_id INNER JOIN sys.schemas sch ON sch.schema_id = tabs.schema_id INNER JOIN sys.types ty ON ty.system_type_id = cols.system_type_id AND ty.user_type_id = cols.user_type_id LEFT JOIN sys.extended_properties ep ON cols.object_id = ep.major_id AND ep.minor_id = cols.column_id; GO CREATE VIEW Documentation.TableComments AS -- Table comments SELECT sch.name AS SchemaName, tabs.name AS TableName, ep.name AS PropertyName, ep.value AS PropertyValue FROM sys.tables tabs INNER JOIN sys.schemas sch ON sch.schema_id = tabs.schema_id LEFT JOIN sys.extended_properties ep ON tabs.object_id = ep.major_id AND ep.minor_id = 0; GO CREATE VIEW Documentation.SchemaComments AS -- Schema comments SELECT sch.name AS SchemaName, ep.name AS PropertyName, ep.value AS PropertyValue FROM sys.schemas sch LEFT JOIN sys.extended_properties ep ON sch.schema_id = ep.major_id WHERE sch.principal_id = 1; GO CREATE VIEW Documentation.ViewComments AS -- View Comments SELECT sch.name AS SchemaName, vw.name AS TableName, ep.name AS PropertyName, ep.value AS PropertyValue FROM sys.views vw INNER JOIN sys.schemas sch ON sch.schema_id = vw.schema_id LEFT JOIN sys.extended_properties ep ON vw.object_id = ep.major_id AND ep.minor_id = 0; GO |
You should now have the following views in your database. I’ve created these in the AdventureWorks sample database.
Documentation.ColumnComments
This view contains information about each tables columns, their data types and comments.
SELECT * FROM Documentation.ColumnComments; |
Documentation.SchemaComments
This view shows the schemas in the database along with any comments.
SELECT * FROM Documentation.SchemaComments; |
Documentation.TableComments
This view exposes comments that have been added to tables.
SELECT * FROM Documentation.TableComments; |
Documentation.ViewComments
This view exposes comments that have been added to views.
SELECT * FROM Documentation.ViewComments; |
