¡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>
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`
```
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:
```bash
OUTPUT <query_result> TO <output_file> WITH (FORMAT='csv')
```
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:
```sql
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
```
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:
Para nuestro caso nos centaremos sobre, **Microsoft.SqlServer.Management.Smo**
Este se separa de la siguiente manera:
### 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"
```
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
```
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")
```
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
```
> 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](https://github.com/rimt07/ps/blob/master/ms-sql-db-dictionary-word-doc.ps1)**
Detallando el archivo este contiene:
```powershell
#==================================================================================
#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()
```
Top comments (0)