Extract Stored Procedure Comments with TSQL

I’ve blogged before about documenting databases. I’m very much a fan of extracting documentation from systems themselves so it’s as up-to-date as it can be. That’s probably why I’m such a big fan of Powershell a tool that excels at this task. This week I was thinking about how to get at the comments often placed at the top of stored procedure definitions. I’m referring to the little block of comments that Microsoft are encouraging us to fill out when we create a new store procedure.

-- =============================================
-- Author:<author>
-- Create date: <create date>
-- Description:	<description>
-- =============================================</description></create></author>

Would it not be useful to get our hands on these comments? Here’s quick TSQL script you can use to attempt to extract those comments.

-- Drop temporary tables if they exist
IF OBJECT_ID('tempdb..#ProcDoc') IS NOT NULL
BEGIN
	DROP TABLE #ProcDoc;
END
 IF OBJECT_ID('tempdb..#temp') IS NOT NULL
 BEGIN
	DROP TABLE #temp;
END

-- Create a table to hold procedure documentation
CREATE TABLE #ProcDoc
(
	Id INTEGER NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
	[Schema] VARCHAR(100),
	[Proc] VARCHAR(100),
	[Author] VARCHAR(100),
	[CreatedDate] VARCHAR(20),
	[Description] TEXT
);

DECLARE @proc VARCHAR(100),
		@schema VARCHAR(100),
		@proc_id INTEGER,
		@schema_proc VARCHAR(200);

-- Cursor to work through our procs
DECLARE procCursor CURSOR LOCAL FAST_FORWARD FOR SELECT p.[name] AS [proc],
							s.[name] AS [schema]
						 FROM sys.procedures p
						 INNER JOIN sys.schemas s
							ON s.schema_id = p.schema_id;

OPEN procCursor;
FETCH NEXT FROM procCursor INTO @proc,
				@schema;

WHILE (@@FETCH_STATUS = 0)
BEGIN

	INSERT INTO #ProcDoc
	(
		[Schema],
		[Proc]
	)
	VALUES
	(
		@Schema,
		@Proc
	);

	SET @proc_id = SCOPE_IDENTITY();

	-- Create a temp table to hold comments
	CREATE TABLE #temp
	(
		[Text] VARCHAR(4000) NULL
	);

	 -- Build schema + proc string
	SET @schema_proc = @schema + '.' + @proc;

	-- sp_helptext to get proc definition
	-- and insert into a temp table
	INSERT INTO #temp
	EXEC sys.sp_helptext @schema_proc;

	-- Just an id we'll use later to identify rows
	ALTER TABLE #temp ADD Id INTEGER NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED;

	-- Get proc author
	UPDATE #ProcDoc
	SET [Author] =
	(
		SELECT (SELECT SUBSTRING([Text], PATINDEX('--Author: ', [text]) + 12, LEN([Text]) - (PATINDEX('--Author: ', [text]) + 12))) AS Author
		FROM #temp
		WHERE [text] LIKE '-- Author:%'
	)
	WHERE Id = @Proc_Id;

	-- Get proc created date
	UPDATE #ProcDoc
	SET CreatedDate =
	(
		SELECT (SELECT SUBSTRING([Text], PATINDEX('--Author: ', [text]) + 17, LEN([Text]) - (PATINDEX('--Author: ', [text]) + 17)))
		FROM #temp
		WHERE [text] LIKE '-- Create date:%'
	)
	WHERE Id = @Proc_Id;

	-- Get proc description
	-- Bit messy here but works for my situation
	-- probably need modification dpending on your commenting habits
	UPDATE #ProcDoc
	SET [Description] = REPLACE(CONVERT(VARCHAR(4000),
	(
		SELECT *
		FROM
		(
			SELECT REPLACE(REPLACE([text], '-- Description:', ''), '-- ', '') AS [text()]
			FROM #temp
			WHERE Id >= (SELECT Id FROM #temp WHERE [text] LIKE '-- Description:%')
			AND Id < (SELECT (Id - 1) FROM #temp WHERE [text] LIKE '%CREATE PROCEDURE%')
		) AS t FOR XML PATH('')
	)), '
', '') -- Replace CR entities
	WHERE ID = @Proc_id;

	-- Drop the temp table
	DROP TABLE #temp;

	-- Get the next row
	FETCH NEXT FROM procCursor INTO @proc,
					@schema;

END

-- Clean up
CLOSE procCursor;
DEALLOCATE procCursor;

-- View procedure documentation
SELECT *
FROM #ProcDoc
WHERE [Description] IS NOT NULL;

All tables are temporary so there’s nothing to clean up. You’ll probably have to tweak this script as your commenting habits will likely vary. If all goes well the script should produce something looking like below.