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  ,,NAME>
-- Create date: <create date,,>
-- Description:	<description  ,,>
-- =============================================

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('')
	)), '&#x0D;', '') -- 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.

extracting stored procedure comments


5 Comments

  1. […] Extract Stored Procedure Comments with TSQL – Cool script for pulling comments out of an stored proc. […]

  2. geographika says:

    Great stuff. I was looking to take comments that other developers had made in SPs and add them to the extended properties so RedGate’s SqlDoc could use them for documentation.
    I used your code (with a few mods due to differing comment use) then added the following code at the end to set the extended properties:

    — Select all records in the #ProcDoc table
    — and replace line breaks with a space for better formatting
    DECLARE metaCursor CURSOR LOCAL FAST_FORWARD
    FOR SELECT [Schema],[Proc],REPLACE(cast([Description] as nvarchar(max)), CHAR(10), ‘ ‘) FROM #ProcDoc;

    OPEN metaCursor;
    FETCH NEXT FROM metaCursor INTO @myschema,
    @myproc, @mydesc;

    WHILE (@@FETCH_STATUS = 0)
    BEGIN

    — Update MS_Description Extended property

    exec sp_executesql N’if (SELECT count(*)
    FROM ::fn_listextendedproperty (@propertyName, @myLevel0Type, @myLevel0Name, @myLevel1Type, @myLevel1Name, @myLevel2Type, @myLevel2Name))> 0
    begin
    EXEC sp_updateextendedproperty @propertyName, @propertyValue, @myLevel0Type, @myLevel0Name, @myLevel1Type, @myLevel1Name, @myLevel2Type, @myLevel2Name;
    end
    else
    begin
    EXEC sp_addextendedproperty @propertyName, @propertyValue, @myLevel0Type, @myLevel0Name, @myLevel1Type, @myLevel1Name, @myLevel2Type, @myLevel2Name;
    end’,
    –make sure the variables below are large enough for the supplied parameters
    N’@propertyName nvarchar(14),@myLevel0Type nvarchar(6),@myLevel0Name nvarchar(3),@myLevel1Type nvarchar(9),@myLevel1Name nvarchar(4000),@myLevel2Type nvarchar(4000),@myLevel2Name nvarchar(4000),@propertyValue nvarchar(4000)’,@propertyName=N’MS_Description’,
    @myLevel0Type=N’SCHEMA’,
    @myLevel0Name=@myschema,
    @myLevel1Type=N’PROCEDURE’,
    @myLevel1Name=@myproc,
    @myLevel2Type=NULL,@myLevel2Name=NULL,
    @propertyValue=@mydesc;

    — Get the next row
    FETCH NEXT FROM metaCursor INTO @myschema,
    @myproc, @mydesc;
    END
    — Clean up
    CLOSE metaCursor;
    DEALLOCATE metaCursor;

  3. Rhys says:

    Cheers, differing commenting habits is a pain we have to live with. Thanks for your input.

    Rhys

  4. Faramarz says:

    Hi thanks for the script.

    1- I think “SELECT (Id – 1) FROM #temp ” in the last lines should change to (Id)

    2- calling Like ‘%Create Procedure%’ assumes on a single spc between create and procedure!

  5. Rhys says:

    Hi Faramarz,

    Yep, as stated, this will probably need to change depending on your commenting habits.

    Cheers,

    Rhys

Leave a Reply