Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Implement a Comment Based Help Framework via CLR Stored Procedure


By:   |   Read Comments   |   Related Tips: More > T-SQL

Problem

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?

Solution

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

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

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

.Example
exec dbo.uspAddTwo 1, 2 -- returns 3

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

.CreatedBy
Jeffrey Yao

.CreatedOn
2016/02/15

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

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);
   
   qry.Parameters.Add(nameParam);


   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());
        ";
   
   conn.Open();
   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);
   }
   else
   {
    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))
       {
        continue;
       }

       if (line.Length >= 2 && line.Substring(0, 2) == "--") //check if the line is commented out
       {
        continue;
       }
       if (rex.IsMatch(line))
       {
        var mt = rex.Match(line);
        key = mt.Groups[1].Value;
        if (!dict.ContainsKey(key))
        { dict.Add(key, ""); }
         
       }
       else
       {
        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)
      {
       sb.AppendLine(v.Key);
       sb.AppendLine(v.Value);
       //sb.AppendLine("");
       DataRow dr = dt.NewRow();
       dr["Item"] = v.Key;
       dr["Description"] = v.Value;
       dt.Rows.Add(dr);
      }
        
      definition = sb.ToString();
     }
    }
   }

   conn.Close();

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

      pipe.SendResultsRow(record);
     }
    }
    finally
    {
     pipe.SendResultsEnd();
    }
   }
   
  }

 }
}

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.

Summary

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)


Last Update:






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.

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools