DEV Community

Cover image for Creación de un diccionario de datos para BD: MS SQL
Şhaʤ
Şhaʤ

Posted on • Updated on

Creación de un diccionario de datos para BD: MS SQL

¡Hola a todos! En este blog, veremos como podemos crear un diccionario de datos con sqlcmd y posteriormente con Powershell para MS SQL y enriquecerlo con etiquetas de columna.

Para aquellos que no estén familiarizados con el término, un diccionario de datos es una herramienta que proporciona información detallada sobre todos los objetos de datos presentes en una base de datos. Estos objetos incluyen tablas, vistas, índices, procedimientos almacenados, funciones y otros elementos.

Un diccionario de datos es esencial para cualquier proyecto de bases de datos, ya que proporciona una visión general completa de la estructura y el contenido de la base de datos. Además, es útil para los desarrolladores, los analistas de datos y otros profesionales de TI que trabajan con bases de datos y necesitan acceder a información detallada sobre los objetos de datos.

En este tutorial, primero veremos como crear un diccionario de datos para MS SQL Server utilizando la herramienta de línea de comandos "sqlcmd". También exploraré algunas de las opciones y comandos que puedes utilizar para personalizar y mejorar tu diccionario de datos. Y después veremos un script generado en PowerShell que nos ayudara a dar un paso mas allá para tener una documentación mas funcional

Por que necesitamos un diccionario de datos?

El diccionario de datos es una herramienta esencial para la administración de bases de datos de SQL. Su objetivo principal es proporcionar información detallada sobre los objetos de datos presentes en una base de datos. Estos objetos incluyen tablas, vistas, índices, procedimientos almacenados, funciones y otros elementos.

Al proporcionar información detallada sobre los objetos de datos de la base de datos, el diccionario de datos puede ayudar a los administradores de bases de datos a:

  • Entender la estructura y el contenido de la base de datos.
  • Identificar y solucionar problemas de rendimiento y errores.
  • Optimizar el rendimiento de la base de datos.
  • Realizar tareas de mantenimiento y actualización de la base de datos.

Documentar y controlar los cambios en la base de datos.
Además, el diccionario de datos es útil para los desarrolladores y los analistas de datos que trabajan con bases de datos y necesitan acceder a información detallada sobre los objetos de datos. También puede ser útil para los usuarios finales que necesitan entender mejor cómo funciona la base de datos y cómo se relacionan los datos entre sí.

Así que, ¿estás listo para aprender a crear tu propio diccionario de datos para MS SQL Server? ¡Comencemos!

En próximos días agregaré pantallas y el resultado de este post con alguna BD de ejemplo

Como crear un diccionario de datos de forma nativa con: sqlcmd

Para crear un diccionario de datos para MS SQL Server utilizando "sqlcmd", sigue estos pasos:

Abre una ventana de línea de comandos y escribe "sqlcmd". Esto abrirá la herramienta "sqlcmd".

Conecta a tu base de datos de MS SQL Server utilizando el comando "CONNECT". Este comando requiere que proporciones el nombre del servidor, la base de datos y las credenciales de inicio de sesión. Por ejemplo:

CONNECT <server_name> -d <database_name> -U <username> -P <password>
Enter fullscreen mode Exit fullscreen mode

Una vez que estés conectado a la base de datos, puedes comenzar a recopilar información sobre los objetos de datos de la base de datos. Para hacerlo, puedes utilizar una variedad de comandos "sqlcmd" y consultas "SELECT". Algunos ejemplos de comandos que puedes utilizar para recopilar información sobre las tablas de la base de datos son:

SELECT * FROM sys.tables
SELECT * FROM information_schema.tables`
Enter fullscreen mode Exit fullscreen mode

Una vez que hayas recopilado toda la información que necesites sobre los objetos de datos de la base de datos, puedes crear un archivo de salida con esa información. Para hacerlo, puedes utilizar el comando "sqlcmd" "OUTPUT". Este comando te permite especificar un archivo de salida y un formato de archivo, como "csv" o "txt". Por ejemplo:

OUTPUT <query_result> TO <output_file> WITH (FORMAT='csv')
Enter fullscreen mode Exit fullscreen mode

Una vez que hayas creado el archivo de salida, puedes abrirlo y verificar que contenga la información correcta. Si necesitas realizar algún cambio o modificación, puedes hacerlo manualmente en el archivo o volver a ejecutar el proceso de recopilación de información utilizando diferentes comandos y opciones.

A continuacion te muestro como se ve el query completo:

sqlcmd

CONNECT MyDatabaseServer

--Obtener una lista de todas las tablas de tu base de datos
SELECT * FROM INFORMATION_SCHEMA.TABLES

--Obtener información detallada sobre una tabla específica
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='MyTable'

--Obtener información sobre las columnas de una tabla,reemplazando "MyTable" por el nombre de la tabla que deseas consultar
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='MyTable'

--Obtener información sobre los índices de una tabla
SELECT * FROM INFORMATION_SCHEMA.INDEXES WHERE TABLE_NAME='MyTable'

OUTPUT MyDictionary.csv WITH (FORMAT='csv')

QUIT
Enter fullscreen mode Exit fullscreen mode

Si bien de forma nativa sqlcmd nos ayuda a obtener información detallada sobre los objetos de datos de la base de datos. Considero que es un esquema un tanto manual, y que no nos permite ampliar mas los datos extraídos por lo que a continuación les mostrare una estrategia de scripting con PowerShell

Como crear un diccionario de datos con Powershell

La estrategia a seguir se detalla en el siguiente diagrama:

xx

Para obtener los detalles de la BD con powershell haremos uso de la libreria: Server Management Objects (SMO) que se divide de la siguiente manera:

Espacios de nombres de SMO

Para nuestro caso nos centaremos sobre, Microsoft.SqlServer.Management.Smo

Este se separa de la siguiente manera:

Detalle de namespace Management.Smo

Obteniendo detalles de una BD con: Microsoft.SqlServer.Management.Smo

Para poder crear un diccionario de datos con powershell, haremos uso de los objetos de powershell para MS SQL como lo es el objeto: Microsoft.SqlServer.Management.Smo

El cual nos ayudara para obtener los detalles de una base de datos (BD), primero debes importar el módulo de SQL Server PowerShell. Esto se puede hacer ejecutando el comando Import-Module SQLPS.

Luego, debes crear una instancia del objeto Server utilizando la cadena de conexión de tu servidor de base de datos. Por ejemplo:

$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList "localhost"
Enter fullscreen mode Exit fullscreen mode

A continuación, puedes utilizar el método Databases del objeto $server para obtener una lista de todas las bases de datos en el servidor. Por ejemplo:

$server.Databases
Enter fullscreen mode Exit fullscreen mode

Para obtener los detalles de una base de datos específica, puedes utilizar el método GetByName del objeto $server.Databases y pasarle el nombre de la base de datos como argumento. Por ejemplo:

$database = $server.Databases.GetByName("miBaseDeDatos")
Enter fullscreen mode Exit fullscreen mode

Una vez que tienes el objeto $database, puedes acceder a sus propiedades para obtener información sobre la base de datos. Por ejemplo, para obtener el tamaño de la base de datos en MB, puedes usar la propiedad Size.

$database.Size
Enter fullscreen mode Exit fullscreen mode

También puedes utilizar los métodos del objeto $database para realizar diferentes acciones, como crear una nueva tabla o ejecutar un script de T-SQL.

Exportando los detalles obtenidos a un archivo HTML

Exportando los detalles obtenidos a un archivo docx

Exportando los detalles obtenidos a un script sql

Script final

A continuación les dejo el script completo:

Para acceder al script completo pueden hacerlo a través de github.com/rimt07/:

ms-sql-db-dictionary-word-doc.ps1

Detallando el archivo este contiene:

#==================================================================================
#Prerequisites: Enable script execution
#Set-ExecutionPolicy Unrestricted -Scope CurrentUser 
#==================================================================================


#==================================================================================
# Variables
$dbServer   =   "Server_Name";
$dbUser     =   "DB_Urer";
$dbPassword =   "DB_Password";
$dbName     =   "DB_Name";
#You need to list, all the catalog names that you want to export data, separated by coma

$CatalogsToDump  = 'ALL'

###Or specific catalog by comma separated
#$CatalogsToDump  = 'Catalog01,Catalog02'

#==================================================================================
$VerbosePreference = "Continue"


# Return all user databases on a sql server 
function getDatabases 
{ 
    param ($sql_server); 
    $databases = $sql_server.Databases | Where-Object {$_.IsSystemObject -eq $false}; 
    return $databases; 
} 

# Get all schemata in a database 
function getDatabaseSchemata 
{ 
    param ($sql_server, $database); 
    $db_name = $database.Name; 
    $schemata = $sql_server.Databases[$db_name].Schemas; 
    return $schemata; 
} 

# Get all tables in a database 
function getDatabaseTables 
{ 
    param ($sql_server, $database); 
    $db_name = $database.Name; 
    $tables = $sql_server.Databases[$db_name].Tables | Where-Object {$_.IsSystemObject -eq $false}; 
    return $tables; 
} 

# Get all stored procedures in a database 
function getDatabaseStoredProcedures 
{ 
    param ($sql_server, $database); 
    $db_name = $database.Name; 
    $procs = $sql_server.Databases[$db_name].StoredProcedures | Where-Object {$_.IsSystemObject -eq $false}; 
    return $procs; 
} 

# Get all user defined functions in a database 
function getDatabaseFunctions 
{ 
    param ($sql_server, $database); 
    $db_name = $database.Name; 
    $functions = $sql_server.Databases[$db_name].UserDefinedFunctions | Where-Object {$_.IsSystemObject -eq $false}; 
    return $functions; 
} 

# Get all views in a database 
function getDatabaseViews 
{ 
    param ($sql_server, $database); 
    $db_name = $database.Name; 
    $views = $sql_server.Databases[$db_name].Views | Where-Object {$_.IsSystemObject -eq $false}; 
    return $views; 
} 

# Get all table triggers in a database 
function getDatabaseTriggers 
{ 
    param ($sql_server, $database); 
    $db_name = $database.Name; 
    $tables = $sql_server.Databases[$db_name].Tables | Where-Object {$_.IsSystemObject -eq $false}; 
    $triggers = $null; 
    foreach($table in $tables) 
    { 
        $triggers += $table.Triggers; 
    } 
    return $triggers; 
} 


# This function buils a list of links for database object types 
function buildLinkList 
{ 
    param ($array, $path);

    #Write-Host $array

    $output = "<ul>"; 

    $outputSchema += "<li><b>Schemas:</b><ul>";
    $outputTrigger += "<li><b>Triggers:</b><ul>";
    $outputTable += "<li><b>Tables:</b><ul>";
    $outputStored += "<li><b>Stored Procedures:</b><ul>";
    $outputObjects += "<li><b>Objects:</b><ul>";

    foreach($item in $array) 
    { 
        if($item.IsSystemObject -eq $false) # Exclude system objects 
        {     
            if([string]$item.GetType() -eq "Microsoft.SqlServer.Management.Smo.Schema") 
            { 
               $outputSchema += "`n<li>$item</li>"; 
            } 
            elseif([string]$item.GetType() -eq "Microsoft.SqlServer.Management.Smo.Trigger") 
            { 
                $outputTrigger += "`n<li>$item</li>"; 
            } 
            elseif([string]$item.GetType() -eq "Microsoft.SqlServer.Management.Smo.Table") 
            { 
               $outputTable += "`n<li>$item</li>"; 
            } 
            elseif([string]$item.GetType() -eq "Microsoft.SqlServer.Management.Smo.StoredProcedure") 
            { 
               $outputStored += "`n<li>$item</li>"; 
            } 
            else
            { 
              $outputObjects += "`n<li>$item</li>"; 
            } 
        } 
    } 

    $output  += $outputSchema +"</ul></li>" + $outputTable +"</ul></li>" + $outputStored +"</ul></li>" + $outputObjects +"</ul></li>";

    $output += "</ul>"; 
    return $output; 
} 

# Return the DDL for a given database object 
function getObjectDefinition 
{ 
    param ($item); 
    $definition = ""; 
    # Schemas don't like our scripting options 
    if([string]$item.GetType() -eq "Microsoft.SqlServer.Management.Smo.Schema") 
    { 
        $definition = $item.Script(); 
    } 
    else 
    { 
        $options = New-Object ('Microsoft.SqlServer.Management.Smo.ScriptingOptions'); 
        $options.DriAll = $true; 
        $options.Indexes = $true; 
        $definition = $item.Script($options); 
    } 
    return "$definition"; 
} 

# This function will get the comments on objects 
# MS calls these MS_Descriptionn when you add them through SSMS 
function getDescriptionExtendedProperty 
{ 
    param ($item); 
    $description = "Empty."; 
    foreach($property in $item.ExtendedProperties) 
    { 
        if($property.Name -eq "MS_Description") 
        { 
            $description = $property.Value; 
        } 
    } 
    return $description; 
} 

# Gets the parameters for a Stored Procedure 
function getProcParameterTable 
{ 
    param ($proc); 
    $proc_params = $proc.Parameters; 
    $prms = $proc_params | ConvertTo-Html -Fragment -Property Name, DataType, DefaultValue, IsOutputParameter; 
    return $prms; 
} 

# Returns a html table of column details for a db table 
function getTableColumnTable 
{ 
    param ($table); 
    $table_columns = $table.Columns; 
    $objs = @(); 
    foreach($column in $table_columns) 
    { 
        $obj = New-Object -TypeName Object; 
        $description = getDescriptionExtendedProperty $column; 
        Add-Member -Name "Name" -MemberType NoteProperty -Value $column.Name -InputObject $obj; 
        Add-Member -Name "DataType" -MemberType NoteProperty -Value $column.DataType -InputObject $obj; 
        #Add-Member -Name "Default" -MemberType NoteProperty -Value $column.Default -InputObject $obj; 
        Add-Member -Name "Identity" -MemberType NoteProperty -Value $column.Identity -InputObject $obj; 
        Add-Member -Name "PK" -MemberType NoteProperty -Value $column.InPrimaryKey -InputObject $obj; 
        Add-Member -Name "FK" -MemberType NoteProperty -Value $column.IsForeignKey -InputObject $obj; 
        Add-Member -Name "Description" -MemberType NoteProperty -Value $description -InputObject $obj; 
        $objs = $objs + $obj; 
    } 
    $cols = $objs | ConvertTo-Html -Fragment -Property Name, DataType, Identity, PK, FK, Description; 
    return $cols; 
} 

# Returns a html table containing trigger details 
function getTriggerDetailsTable 
{ 
    param ($trigger); 
    $trigger_details = $trigger | ConvertTo-Html -Fragment -Property IsEnabled, CreateDate, DateLastModified, Delete, DeleteOrder, Insert, InsertOrder, Update, UpdateOrder; 
    return $trigger_details; 
} 


 # Simple to function to write html pages 
function writeHtmlPage 
{ 
    param ($title, $heading, $body, $filePath); 
    $html = "<html> 
             <head> 
                 <title>$title</title> 

                 <style>
                    pre { margin-left:50px;  max-width:800px; white-space: pre-wrap; white-space: -moz-pre-wrap; white-space: -o-pre-wrap; background: #D3D3D3; font-family:Tahoma;  font-size:8pt; border: 1px solid #bebab0;}
                    code { width:750px; white-space: pre-wrap; white-space: -moz-pre-wrap; white-space: -o-pre-wrap; display: block; padding: 3em 0.5em 3em 0em; }

                    body { font-family:Verdana;    font-size:10pt; }
                    h1 { font-family:Arial;    font-size:12pt; }
                    h2 { font-family:Arial;    font-size:18pt; }
                    h3 { font-family:Arial;    font-size:14pt; }
                    h4 { font-family:Arial;    font-size:14pt; }
                    td, th { border:1px solid black; border-collapse:collapse; }
                    th { color:white; background-color:black; }
                    table, tr, td, th { padding: 2px; margin: 0px font-family:Verdana;  font-size:10pt;}
                    table { margin-left:50px; /*background-color:#D3D3D3;*/ font-family:Verdana;  font-size:10pt;}

                    blockquote { width:750px; white-space: pre-wrap; white-space: -moz-pre-wrap; white-space: -o-pre-wrap; background: #D3D3D3;  border-left: 10px solid black;  margin: 1.5em 10px 1.5em 50px;  padding: 0.5em 10px;  quotes: '\201C''\201D''\2018''\2019';}
                    blockquote:before {  color: black;  content: open-quote;  font-size: 4em;  line-height: 0.1em;  margin-right: 0.25em;  vertical-align: -0.4em;}
                    blockquote p {  display: inline;}
                </style>
             </head>

             <body style='background-color:snow;font-family:Verdana;color:black;font-size:15px;'> 
                 <h1>$heading</h1> 
                $body 
             </body> 
             </html>";
    $html | Out-File -FilePath $filePath; 
} 


Function New-WordText {
    Param (
        [string]$Text,
        [int]$Size = 10,
        [string]$Style = 'Normal',
        [Microsoft.Office.Interop.Word.WdColor]$ForegroundColor = "wdColorAutomatic",
        [switch]$Bold,
        [switch]$Italic,
        [switch]$NoNewLine
    )  
    Try {
        $Selection.Style = $Style
    } Catch {
        Write-Warning "Style: `"$Style`" doesn't exist! Try another name."
        Break
    }

    If ($Style -notmatch 'Title|^Heading'){
        $Selection.Font.Size = $Size  
        If ($PSBoundParameters.ContainsKey('Bold')) {
            $Selection.Font.Bold = 1
        } Else {
            $Selection.Font.Bold = 0
        }
        If ($PSBoundParameters.ContainsKey('Italic')) {
            $Selection.Font.Italic = 1
        } Else {
            $Selection.Font.Italic = 0
        }          
        $Selection.Font.Color = $ForegroundColor
    }

    $Selection.TypeText($Text)

    If (-NOT $PSBoundParameters.ContainsKey('NoNewLine')) {
        $Selection.TypeParagraph()
    }
}

Function New-WordTable {
    [cmdletbinding(
        DefaultParameterSetName='Table'
    )]
    Param (
        [parameter()]
        [object]$WordObject,
        [parameter()]
        [object]$Object,
        [parameter()]
        [int]$Columns,
        [parameter()]
        [int]$Rows,
        [parameter(ParameterSetName='Table')]
        [switch]$AsTable,
        [parameter(ParameterSetName='List')]
        [switch]$AsList,
        [parameter()]
        [string]$TableStyle,
        [parameter()]
        [Microsoft.Office.Interop.Word.WdDefaultTableBehavior]$TableBehavior = 'wdWord9TableBehavior',
        [parameter()]
        [Microsoft.Office.Interop.Word.WdAutoFitBehavior]$AutoFitBehavior = 'wdAutoFitContent'
    )
    #Specifying 0 index ensures we get accurate data from a single object
    $Properties = $Object[0].psobject.properties.name
    $Range = @($WordObject.Paragraphs)[-1].Range
    $Table = $WordObject.Tables.add(
    $WordObject.Range,$Rows,$Columns,$TableBehavior, $AutoFitBehavior)

    Switch ($PSCmdlet.ParameterSetName) {
        'Table' {
            If (-NOT $PSBoundParameters.ContainsKey('TableStyle')) {
                #$Table.Style = "Medium Shading 1 - Accent 1"
            }
            $c = 1
            $r = 1
            #Build header
            $Properties | ForEach {
                Write-Verbose "Adding $($_)"
                $Table.cell($r,$c).range.Bold=1
                $Table.cell($r,$c).range.text = $_
                $c++
            }  
            $c = 1    
            #Add Data
            For ($i=0; $i -lt (($Object | Measure-Object).Count); $i++) {
                $Properties | ForEach {
                    $Table.cell(($i+2),$c).range.Bold=0
                    $Table.cell(($i+2),$c).range.text = $Object[$i].$_
                    $c++
                }
                $c = 1 
            }                 
        }
        'List' {
            If (-NOT $PSBoundParameters.ContainsKey('TableStyle')) {
                #$Table.Style = "Light Shading - Accent 1"
            }
            $c = 1
            $r = 1
            $Properties | ForEach {
                $Table.cell($r,$c).range.Bold=1
                $Table.cell($r,$c).range.text = $_
                $c++
                $Table.cell($r,$c).range.Bold=0
                $Table.cell($r,$c).range.text = $Object.$_
                $c--
                $r++
            }
        }
    }
}



function Write-ProgressHelper {
    param(
        [int]$StepNumber,
        [string]$Message
    )
    $Message = "Step: " + $StepNumber + ", from: " + $steps + ": " + $Message
    Write-Progress -Activity $Activity -Status $Message -PercentComplete (($StepNumber / $steps) * 100)
}


# This function creates all the html pages for our database objects 
function Create-Pages-From-Db-Objects 
{ 
    param ($objectName, $objectArray, $filePath, $db); 

    #Validate-Existent-Directory $filePath + $db.Name
    Validate-Existent-Directory $($filePath + $db.Name + "\$objectName")

    # Create index page for object type 
    $page = $filePath + $($db.Name) + "\index.html"; 
    $list = buildLinkList $objectArray ""; 
      #writeHtmlPage $objectName $objectName $list $page; 
    $body= $list ;

    if($objectArray -eq $null) 
    { 
        $list = "No $objectName in $db"; 
    } 

    # Individual object pages 
    if($objectArray.Count -gt 0) 
    { 
        foreach ($item in $objectArray) 
        { 
            if($item.IsSystemObject -eq $false) # Exclude system objects 
            { 

                $title = "<h1>Database objects</h1>"; 
                $body += "<h2>$item</h2>";

                $description = getDescriptionExtendedProperty($item); 

                $body += "<h4>Description</h4><blockquote><p>$description</p></blockquote>";
                $definition = getObjectDefinition $item; 


                if([string]$item.GetType() -eq "Microsoft.SqlServer.Management.Smo.Schema") 
                { 
                    $body += ""; 
                } 
                else 
                { 
                    if(([string]$item.GetType() -eq "Microsoft.SqlServer.Management.Smo.StoredProcedure") -or ([string]$item.GetType() -eq "Microsoft.SqlServer.Management.Smo.UserDefinedFunction")) 
                    { 
                        Write-Verbose "Generating Stored Procedure or Function: $item"
                        $proc_params = getProcParameterTable $item; 
                        $body += "<h4>Parameters</h4>$proc_params"; 
                    } 
                    elseif([string]$item.GetType() -eq "Microsoft.SqlServer.Management.Smo.Table") 
                    { 
                        Write-Verbose "Generating Table: $item"
                        $cols = getTableColumnTable $item; 
                        $body += "<h4>Columns</h4>$cols"; 
                    } 
                    elseif([string]$item.GetType() -eq "Microsoft.SqlServer.Management.Smo.View") 
                    { 
                        Write-Verbose "Generating View: $item"
                        $cols = getTableColumnTable $item; 
                        $body += "<h4>Columns</h4>$cols</pre>"; 
                    } 
                    elseif([string]$item.GetType() -eq "Microsoft.SqlServer.Management.Smo.Trigger") 
                    { 
                        Write-Verbose "Generating Trigger: $item"
                        $trigger_details = getTriggerDetailsTable $item; 
                        $body += "<h4>Details</h4>$trigger_details"; 
                    }                     
                } 

            } 

        }  
         writeHtmlPage $title $title $body $page; 
    } 
} 


function Validate-Existent-Directory {
    param(
       $p
    )
     if(-Not ($p | Test-Path) ){
               New-Item -Path $p -ItemType Directory | out-null
            }
}



function getDataOfTable2
{
    param 
    (
      $server,
      $db,
      $schema,
      $table
    ) 

    $dir ="C:\database_documentation\" 
    $dirCatalogsData = $dir + "/"+ $dbName + "/catalogs_data/" + $schema
    $dirCatalogsScripts = $dir + "/"+ $dbName + "/catalogs_scripts/" + $schema
    $dirCatalogsScriptsData = $dir + "/"+ $dbName + "/catalogs_inserts/" + $schema
    #---
    Validate-Existent-Directory $($dirCatalogsData)
    #---
    Validate-Existent-Directory $($dirCatalogsScripts)
    #---
    Validate-Existent-Directory $($dirCatalogsScriptsData)


    $tableName = $table.Name

    Write-Verbose "        Bulking Data of: $schema.$tableName"; 

    $scripter = new-object Microsoft.SqlServer.Management.Smo.Scripter($server);
    $scripter.options.ScriptBatchTerminator = $true
    $scripter.options.FileName = $dirCatalogsScriptsData +"/"+ $table.Name + "InsertCatalog.sql"
    $scripter.Options.ScriptData = $true
    $scripter.Options.ScriptSchema  = $false
    $scripter.Options.ToFileOnly = $True
    $scripter.options.Permissions = $false
    $scripter.options.DriAll = $true
    $scripter.options.Triggers = $false
    $scripter.options.Indexes = $false    
    $scripter.EnumScript([Microsoft.SqlServer.Management.Smo.SqlSmoObject[]]$table);


    $scripter = new-object Microsoft.SqlServer.Management.Smo.Scripter($server);
    $scripter.options.ScriptBatchTerminator = $true
    $scripter.options.FileName = $dirCatalogsScripts +"/"+ $table.Name + ".sql"
    $scripter.Options.ScriptData = $false
    $scripter.Options.ScriptSchema  = $true
    $scripter.Options.ToFileOnly = $True
    $scripter.options.Permissions = $false
    $scripter.options.DriAll = $true
    $scripter.options.Triggers = $false
    $scripter.options.Indexes = $true    
    $scripter.options.DriForeignKeys = $true
    $scripter.options.DriForeignKeys = $true
    $scripter.options.SchemaQualifyForeignKeysReferences= $true


    $scripter.EnumScript([Microsoft.SqlServer.Management.Smo.SqlSmoObject[]]$table);


    $Delimiter = ','
    $Header =  '1'
    $TablesR = $db.ExecuteWithResults("Select * FROM " + $schema+ "." + $tableName)


    Validate-Existent-Directory $($dirCatalogsData)


    $result = $TablesR[0].tables.item(0)

    if ($Header -eq '1')
    {
        $result |export-csv –notype -path $dirCatalogsData"/"$tableName.csv  -Encoding ASCII
    }
    else
    {
        $result | ConvertTo-Csv -NoTypeInformation | Select-Object -Skip 1 | Out-File -FilePath $dirCatalogsData"/"$tableName.csv  -Encoding ASCII
    }

}


Function Connect-SqlServer
{   
    [CmdletBinding()]
    param (
        [Parameter(Mandatory = $true)]
        [object]$SqlServer,
        [System.Management.Automation.PSCredential]$SqlCredential,
        [switch]$ParameterConnection,
        [switch]$RegularUser
    )


    if ($SqlServer.GetType() -eq [Microsoft.SqlServer.Management.Smo.Server])
    {

        if ($ParameterConnection)
        {
            $paramserver = New-Object Microsoft.SqlServer.Management.Smo.Server
            $paramserver.ConnectionContext.ConnectTimeout = 2
            $paramserver.ConnectionContext.ApplicationName = "PowerShell module"
            $paramserver.ConnectionContext.ConnectionString = $SqlServer.ConnectionContext.ConnectionString

            if ($SqlCredential.username -ne $null)
            {
                $username = ($SqlCredential.username).TrimStart("\")

                if ($username -like "*\*")
                {
                    $username = $username.Split("\")[1]
                    $authtype = "Windows Authentication with Credential"
                    $server.ConnectionContext.LoginSecure = $true
                    $server.ConnectionContext.ConnectAsUser = $true
                    $server.ConnectionContext.ConnectAsUserName = $username
                    $server.ConnectionContext.ConnectAsUserPassword = ($SqlCredential).GetNetworkCredential().Password
                }
                else
                {
                    $authtype = "SQL Authentication"
                    $server.ConnectionContext.LoginSecure = $false
                    $server.ConnectionContext.set_Login($username)
                    $server.ConnectionContext.set_SecurePassword($SqlCredential.Password)
                }
            }

            $paramserver.ConnectionContext.Connect()
            return $paramserver
        }

        if ($SqlServer.ConnectionContext.IsOpen -eq $false)
        {
            $SqlServer.ConnectionContext.Connect()
        }
        return $SqlServer
    }

    $server = New-Object Microsoft.SqlServer.Management.Smo.Server $SqlServer
    $server.ConnectionContext.ApplicationName = "PowerShell module"

    try
    {
        if ($SqlCredential.username -ne $null)
        {
            $username = ($SqlCredential.username).TrimStart("\")

            if ($username -like "*\*")
            {
                $username = $username.Split("\")[1]
                $authtype = "Windows Authentication with Credential"
                $server.ConnectionContext.LoginSecure = $true
                $server.ConnectionContext.ConnectAsUser = $true
                $server.ConnectionContext.ConnectAsUserName = $username
                $server.ConnectionContext.ConnectAsUserPassword = ($SqlCredential).GetNetworkCredential().Password
            }
            else
            {
                $authtype = "SQL Authentication"
                $server.ConnectionContext.LoginSecure = $false
                $server.ConnectionContext.set_Login($username)
                $server.ConnectionContext.set_SecurePassword($SqlCredential.Password)
            }
        }
    }
    catch { }

    try
    {
        if ($ParameterConnection)
        {
            $server.ConnectionContext.ConnectTimeout = 10
        }
        else
        {
            $server.ConnectionContext.ConnectTimeout = 11
        }

        $server.ConnectionContext.Connect()
    }
    catch
    {
        $message = $_.Exception.InnerException.InnerException
        $message = $message.ToString()
        $message = ($message -Split '-->')[0]
        $message = ($message -Split 'at System.Data.SqlClient')[0]
        $message = ($message -Split 'at System.Data.ProviderBase')[0]
        throw "Can't connect to $sqlserver`: $message "
    }

    if ($ParameterConnection -eq $false)
    {
        if ($server.VersionMajor -eq 8)
        {
            # 2000
            $server.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.Database], 'ReplicationOptions', 'Collation', 'CompatibilityLevel', 'CreateDate', 'ID', 'IsAccessible', 'IsFullTextEnabled', 'IsUpdateable', 'LastBackupDate', 'LastDifferentialBackupDate', 'LastLogBackupDate', 'Name', 'Owner', 'PrimaryFilePath', 'ReadOnly', 'RecoveryModel', 'Status', 'Version')
            $server.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.Login], 'CreateDate', 'DateLastModified', 'DefaultDatabase', 'DenyWindowsLogin', 'IsSystemObject', 'Language', 'LanguageAlias', 'LoginType', 'Name', 'Sid', 'WindowsLoginAccessType')
        }


        elseif ($server.VersionMajor -eq 9 -or $server.VersionMajor -eq 10)
        {
            # 2005 and 2008
            $server.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.Database], 'ReplicationOptions', 'BrokerEnabled', 'Collation', 'CompatibilityLevel', 'CreateDate', 'ID', 'IsAccessible', 'IsFullTextEnabled', 'IsMirroringEnabled', 'IsUpdateable', 'LastBackupDate', 'LastDifferentialBackupDate', 'LastLogBackupDate', 'Name', 'Owner', 'PrimaryFilePath', 'ReadOnly', 'RecoveryModel', 'Status', 'Trustworthy', 'Version')
            $server.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.Login], 'AsymmetricKey', 'Certificate', 'CreateDate', 'Credential', 'DateLastModified', 'DefaultDatabase', 'DenyWindowsLogin', 'ID', 'IsDisabled', 'IsLocked', 'IsPasswordExpired', 'IsSystemObject', 'Language', 'LanguageAlias', 'LoginType', 'MustChangePassword', 'Name', 'PasswordExpirationEnabled', 'PasswordPolicyEnforced', 'Sid', 'WindowsLoginAccessType')
        }

        else
        {
            # 2012 and above
            $server.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.Database], 'ReplicationOptions', 'ActiveConnections', 'AvailabilityDatabaseSynchronizationState', 'AvailabilityGroupName', 'BrokerEnabled', 'Collation', 'CompatibilityLevel', 'ContainmentType', 'CreateDate', 'ID', 'IsAccessible', 'IsFullTextEnabled', 'IsMirroringEnabled', 'IsUpdateable', 'LastBackupDate', 'LastDifferentialBackupDate', 'LastLogBackupDate', 'Name', 'Owner', 'PrimaryFilePath', 'ReadOnly', 'RecoveryModel', 'Status', 'Trustworthy', 'Version')
            $server.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.Login], 'AsymmetricKey', 'Certificate', 'CreateDate', 'Credential', 'DateLastModified', 'DefaultDatabase', 'DenyWindowsLogin', 'ID', 'IsDisabled', 'IsLocked', 'IsPasswordExpired', 'IsSystemObject', 'Language', 'LanguageAlias', 'LoginType', 'MustChangePassword', 'Name', 'PasswordExpirationEnabled', 'PasswordHashAlgorithm', 'PasswordPolicyEnforced', 'Sid', 'WindowsLoginAccessType')
        }
    }

    return $server
}



function Document-All-DataBase-Objects
{
     param (
        [object]$SqlServer,
        [object]$db
    )
   # Get schemata for the current database 
    $schemata = getDatabaseSchemata $sql_server $db; 
    Write-ProgressHelper -Message "Documented schemas" -StepNumber ($stepCounter++)

    # Get tables for the current database 
    $tables = getDatabaseTables $sql_server $db;
    Write-ProgressHelper -Message "Documented tables" -StepNumber ($stepCounter++)

    # Get views for the current database 
    $views = getDatabaseViews $sql_server $db; 
    Write-ProgressHelper -Message "Documented views" -StepNumber ($stepCounter++)


    # Get procs for the current database 
    #$procs = getDatabaseStoredProcedures $sql_server $db; 
    #Create-Pages-From-Db-Objects "Stored Procedures" $procs $filePath $db; 
    #Write-Host "Documented stored procedures"; 

    # Get functions for the current database 
    #$functions = getDatabaseFunctions $sql_server $db; 
    #Create-Pages-From-Db-Objects "Functions" $functions $filePath $db; 
    #Write-Host "Documented functions"; 

    # Get triggers for the current database 
    $triggers = getDatabaseTriggers $sql_server $db; 
    Write-ProgressHelper -Message "Documented triggers" -StepNumber ($stepCounter++)

    $all = $schemata + $tables + $views + $procs + $functions + $triggers  ;

    Return $all;
}

function Create-Word-Document-From-Database
{
   $oldwd = [Environment]::CurrentDirectory
    [Environment]::CurrentDirectory = $pwd
    $html = $filePath + $db.Name + "\index.html";
    $docx = $filePath + $db.Name + "\"+ $db.Name + ".docx";
    [Environment]::CurrentDirectory = $oldwd

    [ref]$SaveFormat = "microsoft.office.interop.word.WdSaveFormat" -as [type] 
    $word = New-Object -ComObject word.application 
    $word.visible = $false 
    $doc = $word.documents.open($html) 

    $Selection = $word.Selection

    ###Create a table of revisions
    New-WordText -Text "Revisions" -Size 24 -Bold
    New-WordText -Text " " -Bold 

    #$obj = New-Object -TypeName Object; 
    #Add-Member -Name "Date" -MemberType NoteProperty -Value "value" -InputObject $obj; 
    #Add-Member -Name "Name" -MemberType NoteProperty -Value "value" -InputObject $obj;    
    #Add-Member -Name "Observations" -MemberType NoteProperty -Value "value" -InputObject $obj; 

    #New-WordTable -Object $obj -Columns 3 -Rows ($obj.Count+1) -AsTable -WordObject $Selection

    #$word.Selection.Start= $doc.Content.Start
    #$Selection = $word.Selection
    #$Selection.TypeParagraph()

    ###Create a table of revisions
    New-WordText -Text "Index" -Size 24 -Bold
    New-WordText -Text " " -Bold 

    $range = $Selection.Range
    $toc = $doc.TablesOfContents.Add($range)
    $Selection.TypeParagraph()

    $doc.saveas([ref] $docx, [ref]$SaveFormat::wdFormatDocumentDefault) 

    ##$word.Visible = $true
    $doc.close() 
    $word.Quit() 
    $word = $null 

    Write-Host "Finish the documents(.docx) generation"
}

function Export-Tables-To-Csv
{
    param (

        [object]$sql_server, 
        [object]$db
    )

    # Get tables for the current database 
    $tables = getDatabaseTables $sql_server $db;

    #----------------------------------------------------------------------------
    #Get Data in csv file
    #----------------------------------------------------------------------------
    Write-Debug "Started getting data"

    foreach ($table in $tables) 
    {
        if(($CatalogsToDump  -match $table.Name) -Or ($CatalogsToDump -eq "ALL") )
        {
          getDataOfTable2 $sql_server $db $table.Schema $table 
        }
    }
    Write-Debug "Finished getting data"
    #----------------------------------------------------------------------------

}

clear

# Progress Bar Variables
$Activity = "Creating Database Dictionary"
$steps = 10
$stepCounter = 0

# Load needed assemblies 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null; 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMOExtended")| Out-Null; 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")| Out-Null; 

$passwordSecure = ConvertTo-SecureString -String $dbPassword -AsPlainText -Force
$credential = [PSCredential]::New($dbUser,$passwordSecure)



Write-ProgressHelper -Message "Initializing data" -StepNumber ($stepCounter++)

Write-ProgressHelper -Message "Started documenting "+ $db.Name -StepNumber ($stepCounter++)

# Root directory where the html documentation will be generated 
$filePath ="C:\database_documentation\" 
Validate-Existent-Directory $filePath


$sql_server  = Connect-SqlServer -SqlServer $dbServer -SqlCredential $credential
$db = $sql_server.Databases.Item($dbName)
# IsSystemObject not returned by default so ask SMO for it 
$sql_server.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.Table], "IsSystemObject"); 
$sql_server.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.View], "IsSystemObject"); 
$sql_server.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.StoredProcedure], "IsSystemObject"); 
$sql_server.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.Trigger], "IsSystemObject"); 


Validate-Existent-Directory $($filePath + $db.Name)

Write-Host "Documenting database objects"
Write-ProgressHelper -Message "Documenting database objects" -StepNumber ($stepCounter++)
$allDocuments = Document-All-DataBase-Objects $sql_server $db

Write-Host "Generate HTML Documentation"
Write-ProgressHelper -Message "Generate HTML Documentation" -StepNumber ($stepCounter++)
Create-Pages-From-Db-Objects "All" $allDocuments $filePath $db; 

Write-Host "Generate WORD Documentation"
Write-ProgressHelper -Message "Generate WORD Documentation" -StepNumber ($stepCounter++)
Create-Word-Document-From-Database

Write-Host "Exporting data to CSV"
Write-ProgressHelper -Message "Exporting data to CSV"  -StepNumber ($stepCounter++)
Export-Tables-To-Csv $sql_server $db

[gc]::collect() 
[gc]::WaitForPendingFinalizers()
Enter fullscreen mode Exit fullscreen mode

Top comments (0)

Read next

djkean profile image

Ultimate Guide on Notification Services - A Technical Overview (2024)

Dennis Kean -

pranavarora1895 profile image

Mastering Angular Component Styling: A Guide to CSS Layering and Dynamic Class Management. No ng:deep required!

Pranav Arora -

backendbro profile image

Step-by-Step Guide: Deploying a Full-Stack App on AWS EC2.

Ukagha Nzubechukwu -

reynolds profile image

Chicken Adobo

Ethan -