Comments in Stored Procedures, SQL Server

Rather than use Extended Properties (not my decision), we want to document our stored procedures at column level.

To do this we are to comment as follows:=

ColA = A + B,

/*ColAA concatanation of A + B*/

We then want to be able to search systables for

Does anyone have any SQL that will retrieve these comments??

Failing that, which systable the comments are held in?

Cheers

Yes multiple ways,

This is how i will do it

Create procedure GetComments
@searchText varchar(1050)
as
begin
CREATE TABLE #tmpComentTable (
ObjectName NVARCHAR(200)
,Comment NVARCHAR(500)
);
DECLARE @Objects TABLE(name varchar(100))
DECLARE @Lines TABLE(id int identity, line varchar(maX))
INSERT @Objects
SELECT Concat(QUOTENAME(SCHEMA_NAME(schema_id)),'.', QUOTENAME(name)) FROM sys.objects
WHERE Type in ('P')
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 @comment = @comment + char(13) + char(10) + line
FROM @Lines
WHERE id between @LineStart and @lineEnd
INSERT INTO #tmpComentTable values (@objectName, @Comment)
DELETE @Lines WHERE id between @LineStart and @lineEnd
END
DELETE @Objects WHERE name = @ObjectName
END
select * from #tmpComentTable where Comment LIKE '%' + @searchText + '%'
drop table #tmpComentTable
END

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sachin Singh4y ago
Accepted answer
yes, for all types, your @objects will be
sys.objects WHERE Type in ('FN', 'IF', 'P', 'TR', 'TF','V')
where 'V' means view
paul walker4y ago
Many thanks Sachin, great. I made one typo in the original question, it's columns in views that I want to comment/query, is a different 'type' that I need to use??
Sachin Singh4y ago
Yes multiple ways,
This is how i will do it
Create procedure GetComments
@searchText varchar(1050)
as
begin
CREATE TABLE #tmpComentTable (

        ObjectName NVARCHAR(200)
        ,Comment NVARCHAR(500)
        );
DECLARE @Objects TABLE(name varchar(100))
DECLARE @Lines TABLE(id int identity, line varchar(maX))

INSERT @Objects
SELECT Concat(QUOTENAME(SCHEMA_NAME(schema_id)),'.', QUOTENAME(name)) FROM sys.objects
WHERE Type in ('P')

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 @comment = @comment + char(13) + char(10) + line
        FROM @Lines
        WHERE id between @LineStart and @lineEnd

        INSERT INTO #tmpComentTable values (@objectName, @Comment)

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


    DELETE @Objects WHERE name = @ObjectName

END
select * from  #tmpComentTable where Comment  LIKE '%' + @searchText + '%'
drop table #tmpComentTable

END