CODEWITHSUNDEEP
sqlsql-server
goofyui
goofyui

Reputation: 3492

How to fetch the code comments from a stored procedure / function and populate to a table?

How to fetch the code comments from a stored procedure / function and populate to a table?

/*
Author : Test
Comment : Test
*/

I am working on a user defined function by passing either a stored procedure or function as input parameter to read the code history comments and store it in a table. Having the detail in a table to maintain the version notes for the input.

Upvotes: 1

Views: 1063

Answers (2)

Daniel Brughera
Daniel Brughera

Reputation: 1651

Check this, there are different ways to get the definition, I prefer sp_helptext because it's already splitted in lines 

DECLARE @Objects TABLE(name varchar(100))
DECLARE @Lines TABLE(id int identity, line varchar(maX))

INSERT @Objects
SELECT name FROM sys.objects WHERE Type in ('FN', 'IF', 'P', 'TR', 'TF') 

DECLARE @ObjectName VARCHAR(100)
WHILE EXISTS (SELECT 1 FROM @Objects)
BEGIN
    SELECT TOP 1 @ObjectName = name FROM @Objects

    DELETE @Lines

    INSERT @Lines (line)
    exec sp_helptext @ObjectName

    DECLARE @Linestart INT, @LineEnd INT
    WHILE EXISTS(SELECT 1 FROM @Lines WHERE charindex('/*', line) > 0)
    BEGIN
        SELECT TOP 1 @Linestart = id
        FROM @Lines WHERE charindex('/*', line) > 0
        ORDER BY id

        SELECT TOP 1 @LineEnd = id
        FROM @Lines WHERE charindex('*/', line) > 0
        ORDER BY id

        DECLARE @comment VARCHAR(MAX) = ''

        SELECT @Coment = @coment + char(13) + char(10) + line
        FROM @Lines 
        WHERE id between @LineStart and @lineEnd

        INSERT INTO yourtable (@objectName, @Comment)

        DELETE @Lines WHERE id between @LineStart and @lineEnd
    END


    DELETE @Objects WHERE name = @ObjectName
END

Upvotes: 2

Hasan Mahmood
Hasan Mahmood

Reputation: 978

You can create a function/stored procedure to achieve this:

CREATE FUNCTION InsertCommentIntoTable
(   
    @Param1 VARCHAR(200)
)
RETURNS int
AS
BEGIN
    -- Declare the return variable here

    DECLARE @str VARCHAR(max)
    SELECT @str = definition  
    FROM sys.sql_modules  
    WHERE object_id = (OBJECT_ID(N'dbo.CustOrderHist'));

    --parse @str string value and do your stuffs: @str has the function and stored procedure codes.

    RETURN 0;

END
GO

Upvotes: 1

Related Questions