How to document SQL code?

Question

When I write R codes, the correct way to document is in the code itself, in the form of comments initiated with a special marking #'.

#' Add together two numbers.
#' 
#' @param x A number.
#' @param y A number.
#' @return The sum of \code{x} and \code{y}.
#' @examples
#' add(1, 1)
#' add(10, 1)
add <- function(x, y) {
  x + y
}

Then there is a tool called roxygen that turns these code comments into a documentation file.

This format keeps the documentation close to the code, making its updates much easier. In addition, it is possible to produce standard documentation documents.

My question is: How to do something like this for SQL (specifically for SQL Server)?

Answer 1

That question "How do you Document your Databases?" of dba.stackexchange has some interesting answers.

Some of the answers suggest using the tools:

• Schemaspy: Java-based (requires Java 5 or higher) that analyzes the metadata of a schema in a database and generates a visual representation of that in a browser-available format.
• Schemacrawler: produces a text file with all database schema objects.
• Apexsql: documents SQL Servers databases, SSAS cubes, SSRS reports and SSIS packages. It creates database documentation in a variety of formats, including compiled help (.CHM), HTML, PDF and MS Word. In addition, the documentation process can be scheduled and run autonomously. If you choose this I advise you to see this part of documentation.
• SQL Power Doc: collection of Windows Powershell scripts and modules that discover, document, and diagnose instances of SQL Server and its underlying Windows operating system configurations.
• Data Dictionary Creator: application that helps to document SQL Server databases. It stores all information in Extended Properties, so it is easier to keep documentation in sync with the database as it changes.

That one reply also looks like a good solution, for SQL Server it uses the extended property and a Powershell script, thus generates table creation scripts for a single table or for all tables in the dbo schema. In this script contains a Create table command keys, primary keys and indexes. Foreign keys are added as comments. Extended table and column properties are added as comments. His script is set to personal encoding style, no individual collations for individual columns and currently requires Sql Server Authentication.

In the reply it shows the complete code to transform the extended properties into a good old ASCII document (BTW is valid sql to recreate your tables):

function Get-ScriptForTable
{
    param (
        $server, 
        $dbname,
        $user,
        $password,
        $filter
    )

[System.reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")  | out-null

$conn = new-object "Microsoft.SqlServer.Management.Common.ServerConnection" 
$conn.ServerInstance = $server
$conn.LoginSecure = $false
$conn.Login = $user
$conn.Password = $password
$conn.ConnectAsUser = $false
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $conn

$Scripter = new-object ("Microsoft.SqlServer.Management.Smo.Scripter")
#$Scripter.Options.DriAll = $false
$Scripter.Options.NoCollation = $True
$Scripter.Options.NoFileGroup = $true
$scripter.Options.DriAll = $True
$Scripter.Options.IncludeIfNotExists = $False
$Scripter.Options.ExtendedProperties = $false
$Scripter.Server = $srv

$database = $srv.databases[$dbname]
$obj = $database.tables

$cnt = 1
$obj | % {

    if (! $filter -or  $_.Name -match $filter)
    {
        $lines = @()
        $header = "---------- {0, 3} {1, -30} ----------"  -f $cnt, $_.Name
        Write-Host $header 

        "/* ----------------- {0, 3} {1, -30} -----------------"  -f $cnt, $_.Name
        foreach( $i in $_.ExtendedProperties)
        {
            "{0}: {1}" -f $i.Name, $i.value
        }
        ""
        $colinfo = @{}
        foreach( $i in $_.columns)
        {
            $info = ""
            foreach ($ep in $i.ExtendedProperties)
            {
                if ($ep.value -match "`n")
                {
                    "----- Column: {0}  {1} -----" -f $i.name, $ep.name
                    $ep.value
                }
                else
                {
                    $info += "{0}:{1}  " -f $ep.name, $ep.value
                }
            }
            if ($info)
            {
                $colinfo[$i.name] =  $info
            }
        }
        ""
        "SELECT COUNT(*) FROM {0}" -f $_.Name
        "SELECT * FROM {0} ORDER BY 1" -f $_.Name
        "--------------------- {0, 3} {1, -30} ----------------- */" -f $cnt, $_.Name
        ""
        $raw = $Scripter.Script($_)
        #Write-host $raw
        $cont = 0
        $skip = $false 
        foreach ($line in $raw -split "\r\n")
        {
            if ($cont -gt 0)
            {
                if ($line -match "^\)WITH ")
                {
                    $line = ")"
                }
                $linebuf += ' ' + $line -replace " ASC", ""
                $cont--
                if ($cont -gt 0) { continue }
            }
            elseif ($line -match "^ CONSTRAINT ")
            {
                $cont = 3
                $linebuf = $line
                continue
            }
            elseif ($line -match "^UNIQUE ")
            {
                $cont = 3
                $linebuf = $line
                $skip = $true
                continue
            }
            elseif ($line -match "^ALTER TABLE.*WITH CHECK ")
            {
                $cont = 1
                $linebuf = "-- " + $line
                continue
            }
            elseif ($line -match "^ALTER TABLE.* CHECK ")
            {
                continue
            }
            else
            {
                $linebuf = $line
            }
            if ($linebuf -notmatch "^SET ")
            {
                if ($linebuf -match "^\)WITH ")
                {
                    $lines += ")"
                }
                elseif ($skip)
                {
                    $skip = $false
                }
                elseif ($linebuf -notmatch "^\s*$")
                {
                    $linebuf = $linebuf -replace "\]|\[", ""
                    $comment = $colinfo[($linebuf.Trim() -split " ")[0]]
                    if ($comment) { $comment = ' -- ' + $comment }
                    $lines += $linebuf + $comment
                }
            }
        }
        $lines += "go"
        $lines += ""
        $block = $lines -join "`r`n"
        $block
        $cnt++
        $used = $false
        foreach( $i in $_.Indexes)
        {
            $out = ''
            $raw = $Scripter.Script($i)
            #Write-host $raw
            foreach ($line in $raw -split "\r\n")
            {
                if ($line -match "^\)WITH ")
                {
                    $out += ")"
                }
                elseif ($line -match "^ALTER TABLE.* PRIMARY KEY")
                {
                    break
                }
                elseif ($line -match "^ALTER TABLE.* ADD UNIQUE")
                {
                    $out += $line -replace "\]|\[", "" -replace " NONCLUSTERED", "" 
                }
                elseif ($line -notmatch "^\s*$")
                {
                    $out += $line -replace "\]|\[", "" -replace "^\s*", "" `
                    -replace " ASC,", ", " -replace " ASC$", "" `
                    <#-replace "\bdbo\.\b", "" #> `
                    -replace " NONCLUSTERED", "" 
                }
                $used = $true
            }
            $block = "$out;`r`ngo`r`n"
            $out
        }
        if ($used)
        {
            "go"
        }
    }
} 
}

You can also make a complete script from a given database:

Get-ScriptForTable 'localhost'  'MyDB' 'sa' 'toipsecret'  |  Out-File  "C:\temp\Create_commented_tables.sql"

Or a filter for a single table:

Get-ScriptForTable 'localhost'  'MyDB' 'sa' 'toipsecret' 'OnlyThisTable'

Beyond these answers to the question "How do you Document your Databases?" has also the DB>doc, which is a tool for generating documentation for Microsoft SQL Server database. It is a console application, used on the command line and controlled by multiple switches. To generate database HTML documentation with a given connection string:

Sqldbdoc "SERVER =. \ SqlExpress; TRUSTED_CONNECTION = yes; DATABASE = AdventureWorks" aw-doc.htm

I personally have never used any of these tools, when I needed documentation on the database I used Reverse Engineering. With the Power Designer, it transforms the database tables into a diagram.

---

These are tools to document the database, to document views, store procedures and functions, you can set a comment with the tag Descripcion:

-- Descripcion: Deleta por nome de Cliente
CREATE PROCEDURE [dbo].[DeleteByName]
@Name nvarchar(255)
AS
BEGIN
SET NOCOUNT ON;
DELETE FROM CLIENT WHERE NAME = @Name
END
GO

Obs.: In the views comments should have the CREATE/ALTER before the GO.

CREATE VIEW [dbo].[VW_SELLER]
AS
SELECT  *
FROM CLIENT
INNER JOIN SELLER ON CLIENT.ID = SELLER.ID
-- Descripcion: Retorna a informação expandidas dos vendedores
GO

The script below gets the information from the documentation entered in the code. It traverses the database objects (views, Stores procedures, functions) and for each object gets the source code with sp_helptext. With the documentation, it shows the object name and its description.

-- ************************************************************************
-- * Obtém a descrição das tabelas, procedures, views e funções *
-- ************************************************************************
SET NOCOUNT ON

DECLARE @DESCRIPCION VARCHAR(4000)
CREATE TABLE #helptext
(
[Text] VARCHAR(4000) NULL
);

DECLARE ShowComentsAll CURSOR
FOR
SELECT sysusers.[name] + '.' + sysobjects.[name] AS FullName
FROM sysobjects
INNER JOIN sysusers ON sysobjects.uid = sysusers.uid
WHERE xtype in ('P', 'V', 'FN')
ORDER BY xtype, FullName

OPEN ShowComentsAll
DECLARE @name VARCHAR(250)

FETCH NEXT FROM ShowComentsAll INTO @name
WHILE @@fetch_status = 0
BEGIN

DELETE #helptext
SET @DESCRIPCION = NULL

INSERT INTO #helptext
exec sp_helptext @name

SELECT @DESCRIPCION = [text]
FROM #helptext
WHERE [text] LIKE '-- Descripcion:%'
IF (@DESCRIPCION IS NOT NULL)
BEGIN
PRINT @NAME
PRINT @DESCRIPCION
END

FETCH NEXT FROM ShowComentsAll INTO @name
END
CLOSE ShowComentsAll
DEALLOCATE ShowComentsAll
DROP TABLE #helptext

Upshot:

dbo.DeleteByName
-- Descripcion: Deleta por nome de Cliente
dbo.VW_SELLER
-- Descripcion: Retorna a informação expandidas dos vendedores