如何从存储过程/函数中获取代码注释并填充到表中?

您可以创建一个函数/存储过程来实现这一点:

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

检查这个,有不同的方法来获得定义,我更喜欢 sp_helptext 因为它已经被分割成几行了

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