SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

USE master;
GO

IF OBJECT_ID(N'dbo.sp_helpmodule', N'P') IS NOT NULL
    DROP PROCEDURE dbo.sp_helpmodule;
GO

/**
author : Doeyull.Kim
e-mail : doeyull.kim@xxxxxx
created date : 2008-02-14
description : Displays the comments of a object of type P, RF, TR, FN, IF and TF
return value :
0 = There is no error.
**/

CREATE PROCEDURE dbo.sp_helpmodule
	@objName nvarchar(128) = NULL,
	@objTypeCode char(2) = NULL
AS

SET NOCOUNT ON;

DECLARE @intReturnValue int;
DECLARE
	@stmt nvarchar(max);

---_# Rollback and return if inside an uncommittable transaction.
IF XACT_STATE() = -1
BEGIN
	SET @intReturnValue = 1;
	ROLLBACK TRANSACTION;
	GOTO ErrorHandler;
END

SET @stmt = N'
DECLARE
	@objType nvarchar(128),
	@definition nvarchar(max),
	@i int,
	@j int,
	@k int,
	@l int,
	@m int,
	@n int,
	@o int,
	@length int,
	@commentLine nvarchar(max),
	@commentLineTemp nvarchar(max),
	@depth int,
	@sequence int,
	@paramName nvarchar(128),
	@paramType nvarchar(128),
	@outputFlag bit,
	@defaultFlag bit,
	@defaultValue nvarchar(max),
	@paramDescription nvarchar(max),
	@stmt_param1 nvarchar(max),
	@stmt_param2 nvarchar(max),
	@stmt_param3 nvarchar(max),
	@stmt_param4 nvarchar(max),
	@stmt_Usage nvarchar(max),
	@stmt_UsageTemp nvarchar(max),
	@intPointer int;

DECLARE @tblObjectLists table (
	seq int IDENTITY(1, 1) NOT NULL,
	objName nvarchar(128) NOT NULL,
	objType nvarchar(128) NOT NULL,
	definition nvarchar(max) NOT NULL
);

DECLARE @tblParameters table (
	seq int IDENTITY(1, 1) NOT NULL,
	paramName nvarchar(128) NOT NULL,
	paramType nvarchar(128) NOT NULL,
	outputFlag bit NOT NULL,
	defaultFlag bit NOT NULL,
	defaultValue nvarchar(max) NOT NULL,
	paramDescription nvarchar(max) NOT NULL
);

DECLARE @tblSequences table (
	depth int NOT NULL DEFAULT(0),
	sequence int NOT NULL DEFAULT(1)
);

INSERT @tblObjectLists (objName, objType, definition)
SELECT O.[name],
	CASE O.[type]
		WHEN ''P'' THEN ''stored procedure''
		WHEN ''RF'' THEN ''replication filter procedure''
		WHEN ''TR'' THEN ''DML trigger''
		WHEN ''FN'' THEN ''scalar function''
		WHEN ''IF'' THEN ''inline table-valued function''
		WHEN ''TF'' THEN ''table-valued-function''
	END, M.definition
FROM sys.objects O
	INNER JOIN sys.sql_modules M ON O.[object_id] = M.[object_id]
WHERE O.[type] = COALESCE(@objTypeCode, O.[type]) AND O.[name] LIKE COALESCE(@objName, O.[name])
	AND O.[type] IN (''P'', ''RF'', ''TR'', ''FN'', ''IF'', ''TF'')
ORDER BY O.[type], O.[name];

SELECT @l = 1, @m = @@ROWCOUNT;

WHILE @l <= @m
BEGIN
	SELECT @objName = objName, @objType = objType, @definition = definition
	FROM @tblObjectLists
	WHERE seq = @l;

	PRINT N''=== dbo.'' + @objName + N'' ==='' + REPLICATE(NCHAR(13) + NCHAR(10), 2);

	SET @i = CHARINDEX(N''/**'', @definition, 1) + 5;

	IF @i = 5
	BEGIN
		SET @l = @l + 1;
		CONTINUE;
	END

	SET @j = CHARINDEX(N''**/'', @definition, 1);
	SET @k = @j + 3
	SET @length = @j - @i - 2;

	PRINT
		N''- Summary'' + CHAR(13) + CHAR(10)
		+ N''================================================================================================'' + CHAR(13) + CHAR(10)
		+ LTRIM(RTRIM(SUBSTRING(@definition, @i, @length))) + NCHAR(13) + NCHAR(10)
		+ N''================================================================================================'' + REPLICATE(CHAR(13) + CHAR(10), 2);

	IF @objType = N''stored procedure''
	BEGIN
		DELETE @tblParameters;

		INSERT @tblParameters (paramName, paramType, outputFlag, defaultFlag, defaultValue, paramDescription)
		SELECT P.[name], 
			CASE TP.[name]
				WHEN N''char'' THEN N''char('' + CAST(P.max_length AS nvarchar(10)) + N'')''
				WHEN N''varchar'' THEN N''varchar('' + CASE P.max_length WHEN -1 THEN N''max'' ELSE CAST(P.max_length AS nvarchar(4)) END + N'')''
				WHEN N''nchar'' THEN N''nchar('' + CAST(P.max_length / 2 AS nvarchar(10)) + N'')''
				WHEN N''nvarchar'' THEN N''nvarchar('' + CASE P.max_length WHEN -1 THEN N''max'' ELSE CAST(P.max_length / 2 AS nvarchar(4)) END + N'')''
				WHEN N''numeric'' THEN N''decimal('' + CAST(P.precision AS nvarchar(10)) + N'','' + CAST(P.scale AS nvarchar(10)) + N'')''
				WHEN N''decimal'' THEN N''decimal('' + CAST(P.precision AS nvarchar(10)) + N'','' + CAST(P.scale AS nvarchar(10)) + N'')''
				WHEN N''binary'' THEN N''binary('' + CAST(P.max_length AS nvarchar(10)) + N'')''
				WHEN N''varbinary'' THEN N''varbinary('' + CASE P.max_length WHEN -1 THEN N''max'' ELSE CAST(P.max_length AS nvarchar(4)) END + N'')''
				ELSE TP.[name]
			END, P.is_output, P.has_default_value,
			CAST(ISNULL(P.default_value, N''NULL'') AS nvarchar(max)),
			ISNULL(CAST(EP.value AS nvarchar(max)), N''no definition'')
		FROM sys.parameters P
			INNER JOIN sys.types TP ON P.user_type_id = TP.user_type_id
			LEFT OUTER JOIN sys.extended_properties EP ON P.[object_id] = EP.major_id AND P.parameter_id = EP.minor_id AND EP.class = 2
		WHERE P.[object_id] = OBJECT_ID(@objName)
		ORDER BY P.parameter_id;

		SELECT @n = MIN(seq), @o = MAX(seq) FROM @tblParameters;

		SET @stmt_param1 = N'''';
		SET @stmt_param2 = N'''';
		SET @stmt_param3 = N'''';
		SET @stmt_param4 = N'''';

		WHILE @n <= @o
		BEGIN
			SELECT @paramName = paramName, @paramType = paramType, @outputFlag = outputFlag, @defaultFlag = defaultFlag,
				@defaultValue = defaultValue, @paramDescription = paramDescription
			FROM @tblParameters
			WHERE seq = @n;

			SET @stmt_param1 = @stmt_param1
				+ CASE @stmt_param1 WHEN N'''' THEN N''DECLARE'' + CHAR(13) + CHAR(10) + N''	  '' ELSE CHAR(13) + CHAR(10) + N''	, '' END
				+ @paramName + N'' '' + @paramType
				+ CASE WHEN @n = @o THEN N'';'' ELSE N'''' END
				+ N''	-- '' + @paramDescription;

			SET @stmt_param2 = @stmt_param2
				+ CASE @stmt_param2 WHEN N'''' THEN N'''' ELSE CASE @outputFlag WHEN 1 THEN N'''' ELSE CHAR(13) + CHAR(10) END END
				+ CASE @outputFlag WHEN 1 THEN N'''' ELSE N''SET '' + @paramName + N'' = NULL;'' END;

			SET @stmt_param3 = @stmt_param3
				+ CASE @stmt_param3 WHEN N'''' THEN N''	  '' ELSE CHAR(13) + CHAR(10) + N''	, '' END
				+ @paramName + N'' = '' + @paramName + CASE @outputFlag WHEN 1 THEN N'' OUTPUT'' ELSE N'''' END
				+ CASE @defaultFlag WHEN 1 THEN N'' -- default : '' + @defaultValue ELSE N'''' END;

			SET @stmt_param4 = @stmt_param4
				+ CASE @outputFlag WHEN 1 THEN CASE @stmt_param4 WHEN N'''' THEN N''SELECT '' ELSE N'', '' END + @paramName + N'' AS ['' + @paramName + N'']'' ELSE N'''' END

			SET @n = @n + 1;
		END;

		SET @stmt_Usage = 
			N''- Usage'' + CHAR(13) + CHAR(10)
			+ N''================================================================================================'' + CHAR(13) + CHAR(10)
			+ N''DECLARE @intReturnValue int;'' + CHAR(13) + CHAR(10)
			+ @stmt_param1 + CASE @stmt_param1 WHEN N'''' THEN N'''' ELSE REPLICATE(CHAR(13) + CHAR(10), 2) END
			+ @stmt_param2 + CASE @stmt_param2 WHEN N'''' THEN N'''' ELSE REPLICATE(CHAR(13) + CHAR(10), 2) END
			+ N''EXEC @intReturnValue = dbo.'' + @objName + CHAR(13) + CHAR(10)
			+ @stmt_param3 + CASE @stmt_param3 WHEN N'''' THEN N'''' ELSE N'';'' + REPLICATE(CHAR(13) + CHAR(10), 2) END
			+ @stmt_param4 + CASE @stmt_param4 WHEN N'''' THEN N'''' ELSE N'';'' + REPLICATE(CHAR(13) + CHAR(10), 2) END
			+ N''PRINT @intReturnValue;'' + CHAR(13) + CHAR(10)
			+ N''================================================================================================'' + REPLICATE(CHAR(13) + CHAR(10), 2);

		IF LEN(@stmt_Usage) <= 4000
			PRINT @stmt_Usage;
		ELSE
		BEGIN
			WHILE 1 = 1
			BEGIN
				SET @stmt_UsageTemp = LEFT(@stmt_Usage, 4000);
				SET @intPointer = (DATALENGTH(@stmt_UsageTemp) / 2) - CHARINDEX(CHAR(10) + CHAR(13), REVERSE(@stmt_UsageTemp)) - 1;
				SET @stmt_UsageTemp = LEFT(@stmt_UsageTemp, @intPointer);
				SET @stmt_Usage = SUBSTRING(@stmt_Usage, @intPointer + 3, (DATALENGTH(@stmt_Usage) / 2) - @intPointer - 2);

				PRINT @stmt_UsageTemp;

				IF LEN(@stmt_Usage) <= 4000
				BEGIN
					PRINT @stmt_Usage;
					BREAK;
				END;
			END;
		END;
	END;

	DELETE @tblSequences;
	INSERT @tblSequences DEFAULT VALUES;

	PRINT
		N''- Flow'' + CHAR(13) + CHAR(10)
		+ N''================================================================================================'' + CHAR(13) + CHAR(10);

	WHILE 1 = 1
	BEGIN
		SET @i = CHARINDEX(N''--_'', @definition, @k) + 3;

		IF @i = 3
			BREAK;

		SET @j = CHARINDEX(NCHAR(13) + NCHAR(10), @definition, @i);
		SET @k = @j + 1;
		SET @length = @j - @i;
		SET @commentLine = LTRIM(RTRIM(SUBSTRING(@definition, @i, @length)));
		SET @commentLineTemp = REVERSE(@commentLine);

		SET @depth = LEN(@commentLine) - CHARINDEX(N''#'', @commentLineTemp);

		UPDATE @tblSequences
		SET @sequence = sequence, sequence = sequence + 1
		WHERE depth = @depth;

		IF @@ROWCOUNT = 0
		BEGIN
			INSERT @tblSequences (depth, sequence) VALUES (@depth, 2);
			SET @sequence = 1;
		END;

		DELETE @tblSequences WHERE depth > @depth;

		PRINT REPLICATE(N''	'', @depth) + CAST(@sequence AS nvarchar(10)) + N''. '' + RTRIM(LTRIM(SUBSTRING(@commentLine, @depth + 2, LEN(@commentLine))));
	END;

	PRINT
		N''================================================================================================''
		+ REPLICATE(CHAR(13) + CHAR(10), 4);

	SET @l = @l + 1;
END;'

EXEC sp_executesql @stmt, N'@objName nvarchar(128), @objTypeCode char(2)', @objName = @objName, @objTypeCode = @objTypeCode;

RETURN 0;

ErrorHandler:
RETURN @intReturnValue;
GO

EXEC sp_addextendedproperty N'MS_Description', N'Displays the comments of a object of type P, RF, TR, FN, IF and TF', N'user', N'dbo', N'procedure', N'sp_helpmodule'
EXEC sp_addextendedproperty N'MS_Description', N'Is the nonqualified name of a object', N'user', N'dbo', N'procedure', N'sp_helpmodule', N'parameter', N'@objName'
EXEC sp_addextendedproperty N'MS_Description', N'Is the type code of a object (P, RF, TR, FN, IF, TF)', N'user', N'dbo', N'procedure', N'sp_helpmodule', N'parameter', N'@objTypeCode'
GO