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

-- 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;