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;