时间戳

首页 > 解决方案 > 如何从存储过程/函数中获取代码注释并填充到表中？

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

问题描述

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

/*
Author : Test
Comment : Test
*/

我正在处理用户定义的函数，方法是将存储过程或函数作为输入参数传递以读取代码历史注释并将其存储在表中。在表格中包含详细信息以维护输入的版本说明。

标签： sqlsql-server

解决方案

检查这个，有不同的方法来获得定义，我更喜欢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

推荐阅读