Categories
Scripts

Database Documentation

Almost every application DBA would have a need to document the various database objects such as tables, views, stored procedures and functions. I wasn’t an exception and exploring ways to document the 400+ tables we have. Basically, this would serve the following purpose:

1. Every one reading the document would have a fair idea as to where the table is being used and for what purpose a table was designed.
2. It makes the life easier for the experienced folks and improves productivity. As they no longer have to spend longer time in teaching the new joiners about the database schema.
Thanks to Microsoft, we have a few extended properties that make our life easier in documenting the database schema. I’ve created the below procedure based on few of those extended properties.

USE [MyDB]


GO


IF NOT EXISTS (SELECT name

FROM sys.objects

WHERE TYPE = 'P'

AND name = 'SetInfiObjectDescription')

BEGIN

EXEC('CREATE PROCEDURE Setinfiobjectdescription AS BEGIN SELECT 1 AS num END')

END


GO


ALTER PROCEDURE [dbo].[Setinfiobjectdescription] @ObjectName NVARCHAR(1000),

@ColumnName NVARCHAR(1000),

@ObjectDesc SQL_VARIANT

AS

BEGIN

IF @ColumnName IS NULL

BEGIN

IF NOT EXISTS(SELECT TOP 1 1

FROM sys.tables AS tbl

INNER JOIN sys.extended_properties AS p

ON p.major_id = tbl.object_id

AND p.class = 1

WHERE tbl.name = @ObjectName

AND p.name = 'TableDescription')

BEGIN

EXEC sys.Sp_addextendedproperty

@name = N'TableDescription',

@value = @ObjectDesc,

@level0type = N'SCHEMA',

@level0name = 'dbo',

@level1type = N'TABLE',

@level1name = @ObjectName

END

ELSE

BEGIN

EXEC sys.Sp_updateextendedproperty

@name=N'TableDescription',

@value= @ObjectDesc,

@level0type=N'SCHEMA',

@level0name=N'dbo',

@level1type=N'TABLE',

@level1name=@ObjectName

END

END

ELSE

BEGIN

IF NOT EXISTS(SELECT TOP 1 1

FROM sys.tables AS tbl

INNER JOIN sys.extended_properties AS p

ON p.major_id = tbl.object_id

AND p.class = 1

LEFT OUTER JOIN sys.all_columns AS clmns

ON clmns.object_id = tbl.object_id

AND p.minor_id = clmns.column_id

WHERE tbl.name = @ObjectName

AND p.name = 'ColumnDescription'

AND clmns.name = @ColumnName)

BEGIN

EXEC sys.Sp_addextendedproperty

@name = N'ColumnDescription',

@value = @ObjectDesc,

@level0type = N'SCHEMA',

@level0name = 'dbo',

@level1type = N'TABLE',

@level1name = @ObjectName,

@level2type = N'Column',

@level2name = @ColumnName

END

ELSE

BEGIN

EXEC sys.Sp_updateextendedproperty

@name=N'ColumnDescription',

@value= @ObjectDesc,

@level0type=N'SCHEMA',

@level0name=N'dbo',

@level1type=N'TABLE',

@level1name=@ObjectName,

@level2type = N'Column',

@level2name=@ColumnName

END

END

END


GO 

The above procedure will add or modify the table or column description accordingly. The modifying as well as inserting logic is handled in the procedure, so just mention the description for the entity, it will overwrite or create the description accordingly.

Following are the steps to add table or column definition:

EXEC dbo.SetInfiObjectDescription

@ObjectName='TableName',

@Columnname=NULL,

@ObjectDesc='Mention about the table and its usage'


EXEC dbo.SetInfiObjectDescription

@ObjectName='TableName',

@Columnname='column name 1',

@ObjectDesc='Mention about the column 1 and its usage'


EXEC dbo.SetInfiObjectDescription

@ObjectName='TableName',

@Columnname='column name 2',

@ObjectDesc='Mention about the column 2 and its usage'

The following query is used to fetch the table/column description:

SELECT tbl.name AS tablename,

clmns.name AS columnname,

p.name AS [Key],

p.VALUE AS DESCRIPTION

FROM sys.tables AS tbl

INNER JOIN sys.extended_properties AS p ON p.major_id = tbl.object_id AND p.class = 1 -- object/column

LEFT OUTER JOIN sys.all_columns AS clmns ON clmns.object_id = tbl.object_id AND p.minor_id = clmns.column_id 

By Deepak

Microsoft Most Valuable Professional (MVP) award-winning Database Designer with 5 years of experience in designing, administering, developing & performance tuning relational databases and data warehouse.

I possess strong leadership skills experience by serving as the Chapter Leader of Professional Association for SQL Server (PASS) which has around 50 members.

I am passionate about solving business problems using my technical skills and hence I am interested in the field of Technology Consulting in Information Management.

Leave a Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

*