Comments in Stored Procedures, SQL Server

Question

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,

/*<COLS>ColA<COLE><CMTS>A concatanation of A + B<CMTE>*/

We then want to be able to search systables for <COLS>

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

Failing that, which systable the comments are held in?

Cheers

Sachin Singh · Answer

yes, for all types, your @objects will be sys.objects WHERE Type in ( 'FN' , 'IF' , 'P' , 'TR' , 'TF','V' ) where 'V' means view

Sachin Singh · Answer

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

paul walker · Answer

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??

Comments in Stored Procedures, SQL Server

Answers (3)