List user defined stored procedures with their description

Question

We have a pretty automated database we use for internal reporting at work. It is running on a lot of stored procedures executed via sqlcmd and as the stored procedure list was growing larger and larger and manually maintaining the documentation of what each of the SPs is doing seemed to me like a torture I started to think of a query that would get me the list of all my user-defined procedures with their built-in-code descriptions.

Sample procedure name - dbo.RemedySelect Description block:

-- =============================================
-- Author:      author_id
-- Create date: 2015/08/03
-- Description: {Insert data from Remedy Report into SRC_REMEDY_RAW table}
-- Batch name:  remedy_import.cmd
-- =============================================

Probably you should rename sp `RemedySelect` 'cause it's actually not a _select proc_. Less mystery in naming - less documentation issues. I'd recommend to upgrade your naming convention and may be a folder structure. It does not seem to me to be any helpful to describe `remedy_import` script with `Inserts data from Remedy`. — Ivan Starostin, Feb 19 '16 at 09:48
Well, it was just an example SP, but you are right naming convention is half of success, right? — Adrian Chrostowski, Feb 19 '16 at 09:57

score 1 · Accepted Answer · answered Feb 19 '16 at 10:08

I would perhaps skip using curly brackets and use this:

select 
    p.object_id                 as [SP_ID]
    ,p.[name]                   as [SP_NAME]
    ,p.create_date              as [CREATE_DATE]
    ,p.modify_date              as [MODIFY_DATE]
    ,ltrim(rtrim(substring(
        c.[definition],
        charindex('-- Description:',c.[definition]) + len('-- Description:') + 1,
        charindex('--', c.[definition], charindex('-- Description:',c.[definition]) + 2) - charindex('-- Description:',c.[definition]) - len('-- Description:') - 3
    ))) as [SP_DESCRIPTION]
from sys.objects p
  join sys.sql_modules c
    on p.object_id = c.object_id
where p.[type] = 'P'
    and p.is_ms_shipped = 0
   and c.[definition] like '%-- Description:%[' + CHAR(13)+CHAR(10)+']%'
order by p.create_date

which would handle one-line descriptions like that:

-- =============================================
-- Author:      author_id
-- Create date: 2015/08/03
-- Description: Insert data from Remedy Report into SRC_REMEDY_RAW table
-- Batch name:  remedy_import.cmd
-- =============================================

Substituting -- Description: with other titles from the comment would enable extraction of other bits of documentation too.

Thanks mate, works like charm. This is why I love SO. You share your idea and someone else contributes to what you've already done making your solution even better! — Adrian Chrostowski, Feb 19 '16 at 11:47

score 0 · Answer 2 · edited May 23 '17 at 12:31

I found a useful query code here on SO (link), modified it a little:

select 
    p.object_id                 as [SP_ID]
    ,p.[name]                   as [SP_NAME]
    ,p.create_date              as [CREATE_DATE]
    ,p.modify_date              as [MODIFY_DATE]
    -- get the description string from between '{' and '}' characters
    ,replace(substring(c.[definition], charindex('{',c.[definition])+1 ,((charindex('}',c.[definition]))-((charindex('{',c.[definition]))))),'}','')as [SP_DESCRIPTION]

from sys.objects p
  join sys.sql_modules c
    on p.object_id = c.object_id
where p.[type] = 'P'
    and p.is_ms_shipped = 0
   and c.[definition] like '%Description%'
order by p.create_date

and here are the results for the above query:

SP_ID       SP_NAME         CREATE_DATE             MODIFY_DATE             SP_DESCRIPTION
1613248802  RemedySelect    2015-08-18 09:17:28.880 2016-02-18 17:32:13.983 Insert data from Remedy Report into SRC_REMEDY_RAW table

I find it very useful when there are dozens or over a hundred stored procedures. It helps me keep the documentation up to date. I will gladly listen to your feedback on both the code and the general approach of maintaining the stored procedures documentation.

List user defined stored procedures with their description

2 Answers2