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
Leave a Reply