Implement a Comment Based Help Framework via CLR Stored Procedure

By:   |   Comments   |   Related: > TSQL


I like PowerShell's comment-based help, it is very useful when you want to check the cmdlet basic information, such as basic function descriptions, parameters or sample examples. All this information is extracted from the comments in the cmdlet source files and use get-help to retrieve the information.

An example is shown below:

PowerShell get-help

I am just wondering whether we can have a similar system in SQL Server, so if I run a T-SQL Help on an object (such as stored procedure, views, user defined functions, etc.) in SQL Server Management Studio (SSMS), I will extract the comment information from the source file?


We first design what these comments look like before we can come up with a solution. So here is the list of key words and the layout of the comments that will be fetched to serve as help information.

  1. The help information should be in the first comment block defined by /* ... */
  2. Inside this block, there cannot be another nested block /* ... */, but we can have line comments, i.e. --
  3. The following keywords (case-insensitive) can be used, each keyword should be preceded with dot [.], you can use a few keys or all keys.
    • .Function
    • .Parameter
    • .Example
    • .CreatedBy
    • .CreatedOn
    • .Modification
    • .Note
  4. Each keyword will be in a line itself, the detailed information should be in one or multiple new lines after each keyword

This is pretty simple and straight-forward and here is an example for a stored procedure.

-- Project: XYZ
-- Copyright: Copyright @ ABC Inc, protected by Federal Law, any violations 
--          : will be prosecuted

Calculate the sum of two integers
The return is an integer.

@i int: 1st integer
@j int: 2nd integer
Both ar not nullable

exec dbo.uspAddTwo 1, 2 -- returns 3

EXEC dbo.uspAddTwo @i=10, @j=20 -- returns 30

Jeffrey Yao


Jeff yao on 2016/02/16, ensure the parameters are not nullable
create proc dbo.uspAddTwo (@i int, @j int )
 if (@i is null or @j is null)
 raiserror ('parameters cannot be null', 16, 1);
  select [sum]= @i + @j;

Assume this stored procedure is created in database [TestDB], the final result of getting the help info of this stored procedure will be:

use [TestDB];
exec master.dbo.sp_help2 @Name='dbo.uspAddTwo', @ShowType='T' -- with tabular result

The result looks like:

result in tabular form

Another result format is simply a string as shown below:

result in text form

C# Code and Explanation

To achieve the expected result, we have to rely on Regular Expressions, and as such, I use C# to create a CLR stored procedure. A few key points are:

  • First figure out which database context master.dbo.sp_help2 is running. The @Name object is in this database.
  • Find if the object @Name exists, if exists, find its definition via sys.sql_modules, if not, reports error and exit.
  • Search the definition retrieved in the previous step for the keywords, and if found, save them to a Dictionary variable
  • According to input parameter [ShowType], if the value is "T", transfer data in the dictionary variable to a DataTable, and send it out
  • If [ShowType] has a value of "S", transfer data in the dictionary variable into a string, and send it out

CLR Stored Procedure Source Code for T-SQL Comments

Below is the code:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
using System.IO;
using Microsoft.SqlServer.Server;
using System.Collections.Generic;
using System.Text;

public partial class StoredProcedures
 [Microsoft.SqlServer.Server.SqlProcedure(Name ="sp_help2")]
 public static void sp_help2 (string Name, string ShowType )
  string definition;
  string database;

  using (SqlConnection conn = new SqlConnection("context connection=true"))
   //DataSet ds = new DataSet();
   DataTable dt = new DataTable();
   dt.Columns.Add(new DataColumn("Item", System.Type.GetType("System.String")));
   dt.Columns.Add(new DataColumn("Description", System.Type.GetType("System.String")));

   SqlCommand qry = new SqlCommand();

   if (ShowType.ToUpper() != "T" && ShowType.ToUpper() != "S")
    throw new System.ArgumentException("Invalid parameter value, valid values are T=Table Format, S=String Format", "ShowType");

   SqlParameter nameParam = new SqlParameter("@Name", SqlDbType.NVarChar);

   nameParam.Value = Name;
   qry.Connection = conn;
   qry.CommandText = @"
        declare @db varchar(128);
        select @db = db_name(resource_database_id) 
         from sys.dm_tran_locks 
         where request_session_id = @@spid  
         and resource_type = 'database' 
         and request_owner_type = 'SHARED_TRANSACTION_WORKSPACE';
        select dbname=isnull(@db, db_name());
   database = (string)qry.ExecuteScalar();

   qry.CommandText = "use [" + database + "]; \r\n";
   qry.CommandText += "select isnull(definition, '') from sys.sql_modules where object_id=object_id(@Name);";

   definition = (String) qry.ExecuteScalar();

   if (string.IsNullOrEmpty(definition) )
    definition = string.Format("[{0}] not found in database [{1}] ", Name, database);
    Dictionary<string, string> dict = new Dictionary<string, string>();
    // initialize the first record
    dict.Add("Name", Name + "\r\n");

    string patt = @"(?s)/\*.+?\*/";
    Regex rex = new Regex(patt);
    Match m = rex.Match(definition);
    // follwing is the key word list, and can be modified to meet your own requirement 
    patt = @"(?i)^\s*\.(function|parameter|example|CreatedBy|CreatedOn|ModifiedBy|Modification|Note):?$"; 
    rex = new Regex(patt);
    string key = string.Empty;
    string value = m.Value.Replace("/*", "");
    value = value.Replace("*/", "");

    if (!string.IsNullOrEmpty(value))
     using (StringReader reader = new StringReader(value))
      string line = string.Empty;
      while ((line = reader.ReadLine()) != null)
       line = line.Trim();
       if (string.IsNullOrEmpty(line))

       if (line.Length >= 2 && line.Substring(0, 2) == "--") //check if the line is commented out
       if (rex.IsMatch(line))
        var mt = rex.Match(line);
        key = mt.Groups[1].Value;
        if (!dict.ContainsKey(key))
        { dict.Add(key, ""); }
        if (!String.IsNullOrEmpty(key))
         dict[key] += line + Environment.NewLine;

     if (dict.Count > 0)
      StringBuilder sb = new StringBuilder("");

      // populate definition with dict values
      foreach(var v in dict)
       DataRow dr = dt.NewRow();
       dr["Item"] = v.Key;
       dr["Description"] = v.Value;
      definition = sb.ToString();


   if (ShowType == "S")
   { SqlContext.Pipe.Send(definition); }
    SqlDataRecord record = new SqlDataRecord(
    new SqlMetaData("item", SqlDbType.VarChar, 4000),
    new SqlMetaData("description", SqlDbType.VarChar, 4000));
    SqlPipe pipe = SqlContext.Pipe;
     foreach (DataRow row in dt.Rows)
      for (int index = 0; index < record.FieldCount; index++)
       string value = row[index].ToString();
       record.SetValue(index, value);



Once published to a SQL Server [master] database, we can start to use it in the format of:

use [User DB] -- where [object name] exists, can be two part naming convention here
exec master..sp_help2 '[object name]', 'S' -- or 'T'

I initially tried to mark sp_help2 as a system object with sp_MS_marksystemobject so I do not need to use three part naming convention to call [sp_help2], but I later found this is not possible with CLR stored procedure as I explained here.


In this tip, we have defined and implemented a comment based help framework similar to PowerShell's help system, it is tested in SQL Server 2016 Developer Edition.

This framework may facilitate the comment standardization for database objects, and if each database object is commented in such a way, we can easily extract the needed information and utilize the information in many other ways, such as documentation or creating object extended properties.

Next Steps

You may read the following articles to better understand SQL Server CLR implementations:

Based on this [sp_help2], we can do the following work easily and with fun.

  • set [ShowType] = 'T' to run sp_help2 and then create extended property for each keyword to this object. (Can be done via T-SQL)
  • set [ShowType] = 'S' to run sp_help2 and then dump the result to a text file. We can loop through all objects in interest and generate a help document. (Better to be done via PowerShell)

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Jeffrey Yao Jeffrey Yao is a senior SQL Server consultant, striving to automate DBA work as much as possible to have more time for family, life and more automation.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

get free sql tips
agree to terms