LISTING A: Code That Creates the CreateDataDictionary Procedure
CREATE PROC CreateDataDictionary
AS
/*
Purpose: Set up description and comment extended properties for each table
and column in the current database.
Input:	None
Output: 	None
Notes:	Bob Pfeiff, June 2001
*/
SET NOCOUNT ON

DECLARE @tablecounter int
DECLARE @colcounter int
DECLARE @tables table
(
ident int identity(1, 1),
objectid int,
tablename nvarchar(128)
)
DECLARE @tablename nvarchar(128)
DECLARE @colname nvarchar(128)

CREATE TABLE #columns
(
ident int identity(1, 1),
objectid int,
colid int,
colname nvarchar(128)
)


INSERT INTO
	@tables
SELECT
	id,
	name
FROM
	sysobjects
WHERE
	xtype = 'u'

SET @tablecounter = @@rowcount

WHILE @tablecounter > 0
--Loop through the tables in the database and add extended properties "MS_Description" and
--"Comment" for each table if the extended properties aren’t present.
	BEGIN	
	SELECT @tablename = tablename FROM @tables WHERE ident = @tablecounter

	IF NOT EXISTS(SELECT * FROM sysproperties AS p JOIN @tables AS t ON p.id = t.objectid 
AND p.smallid = 0 WHERE t.ident = @tablecounter  AND p.name = 'MS_Description')
		BEGIN
		EXEC sp_addextendedproperty 'MS_Description', null, 'user', dbo, 'table', @tablename
		END
	IF NOT EXISTS(SELECT * FROM sysproperties AS p JOIN @tables AS t ON p.id = t.objectid 
AND p.smallid = 0 WHERE t.ident = @tablecounter  AND p.name = 'Comment')
		BEGIN
		EXEC sp_addextendedproperty 'Comment', null, 'user', dbo, 'table', @tablename
		END
	
	--Get the list of columns for the current table.

	INSERT INTO
		#columns
	SELECT
		id,
		colid,
		name
	FROM
		syscolumns
	WHERE
		id = object_id(@tablename)

	SET @colcounter = @@rowcount
	
	WHILE @colcounter > 0
		BEGIN
		SELECT @colname = colname FROM #columns WHERE ident = @colcounter

		IF NOT EXISTS(SELECT * FROM sysproperties AS p JOIN #columns AS c ON p.smallid 
= c.colid AND p.id = c.objectid WHERE c.ident = @colcounter AND p.name = 'MS_Description')
			BEGIN
			EXEC sp_addextendedproperty 'MS_Description', null, 'user', dbo, 'table', 
@tablename, 'column', @colname
			END
		IF NOT EXISTS(SELECT * FROM sysproperties AS p JOIN #columns AS c ON p.smallid 
= c.colid AND p.id = c.objectid WHERE c.ident = @colcounter AND p.name = 'Comment')
			BEGIN
			EXEC sp_addextendedproperty 'Comment', null, 'user', dbo, 'table', @tablename, 
'column', @colname
			END
		SET @colcounter = @colcounter - 1
		END
	truncate table #columns
	SET @tablecounter = @tablecounter - 1
	END
RETURN