How to document codes in SQL?

21

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

#' 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 transforms these code comments into a documentation file.

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

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

    
asked by Daniel Falbel 19.12.2016 в 13:10

1 answer

10

This 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 rather than in a browser-viewable 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 advice, see part of the documentation.
  • SQL Power Doc : A collection of Windows PowerShell scripts and modules that discover, document, and diagnose instances of SQL Server and its underlying operating system configurations Windows.
  • Data Dictionary Creator : An application that helps you document SQL Server databases. It stores all information in Extended Properties, so it's easier to keep the documentation in sync with the database as it changes.

This response also seems like a good solution, for SQL Server it uses the extended properties and a PowerShell script, so it generates table-creation scripts for a single table or for all tables in the dbo schema. This script contains a Create table of command, primary keys, and indexes. Foreign keys are added as comments. The extended properties of table and table columns are added as comments. His script is set to personal encoding style, without individual collation for individual columns, and currently requires Sql Server Authentication.

In answer it shows the complete code to transform the extended properties into a good old ASCII document (BTW is valid sql to re-create 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 of 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'

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

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

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

These are tools for documenting the database, for 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

Note: In views comments should have CREATE/ALTER before 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 inserted 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 name of the object 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

Result:

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