Public/Export-mssDatabaseDocumentation.TempPoint.ps1
|
<#
.SYNOPSIS Erstellt eine vollständige technische Dokumentation einer SQL Server-Datenbank. .DESCRIPTION Dokumentiert alle relevanten Objekte einer Datenbank und exportiert das Ergebnis als strukturierten HTML-Bericht und/oder TXT-Datei. DOKUMENTIERTE OBJEKTE: Datenbank-Eigenschaften - Recovery-Modell, Kompatibilitätsgrad, Collation, Größe, Eigentümer - Erstelldatum, letztes Backup, TDE-Status, Auto-Grow-Einstellungen Dateigruppen und Dateien - Alle Dateigruppen mit Dateipfaden, Größe, freiem Speicher, Autogrow Tabellen - Alle Spalten mit Datentyp, Länge, Nullable, Default, Identity - Primärschlüssel, Fremdschlüssel, Indizes (inkl. Fragmentierungsgrad) - Row-Count und Größe Views - Name, Schema, Erstelldatum, is_updatable, Definition (erste 500 Zeichen) Stored Procedures - Name, Schema, Erstelldatum, Parameterliste Funktionen - Scalar, Table-valued: Name, Schema, Typ, Rückgabetyp, Parameter Trigger - Name, Tabelle, Ereignisse (INSERT/UPDATE/DELETE), AFTER/INSTEAD OF, aktiv/inaktiv Rollen und Benutzer - Datenbankbenutzer mit Login-Mapping und Rollenzugehörigkeit - Datenbankrollen und deren Mitglieder .PARAMETER SqlInstance SQL Server-Instanz (Standard: aktueller Computername). .PARAMETER SqlCredential PSCredential für die Verbindung. .PARAMETER Database Name der zu dokumentierenden Datenbank. Pflichtfeld. .PARAMETER OutputPath Zielverzeichnis für die Ausgabedateien. Standard: aus Modulkonfiguration. .PARAMETER Format Ausgabeformat: HTML, TXT oder Both. Standard: Both. .PARAMETER IncludeDefinitions Vollständige T-SQL-Definitionen von Views, Procedures und Funktionen einbeziehen. Standard: $false (nur Metadaten). .PARAMETER IncludeIndexFragmentation Fragmentierungsgrad der Indizes ermitteln (via sys.dm_db_index_physical_stats). Kann auf großen Datenbanken langsam sein. Standard: $false. .PARAMETER ExcludeTable Tabellen ausschließen. Wildcards erlaubt (z.B. 'tmp*', 'log_*'). .PARAMETER EnableException Ausnahmen sofort auslösen. .EXAMPLE Export-mssDatabaseDocumentation -SqlInstance "SQL01" -Database "AdventureWorks" .EXAMPLE # Mit vollständigen Definitionen und in bestimmtes Verzeichnis Export-mssDatabaseDocumentation -SqlInstance "SQL01" -Database "ProdDB" ` -IncludeDefinitions -OutputPath "D:\Docs" -Format HTML .EXAMPLE # Ohne Fragmentierungsanalyse (schneller) Export-mssDatabaseDocumentation -SqlInstance "SQL01" -Database "CORONA" ` -Format TXT .NOTES Erfordert: dbatools, Invoke-mssLogging, Get-mssDefaultOutputPath, Copy-mssToCentralPath Benötigt VIEW DEFINITION auf der Datenbank und VIEW DATABASE STATE. Fragmentierungsabfrage benötigt ALTER TRACE oder sysadmin (sys.dm_db_index_physical_stats). Die HTML-Ausgabe ist selbst-enthalten (kein externes CSS/JS). Große Datenbanken (>1000 Tabellen): -IncludeIndexFragmentation weglassen. #> function Export-mssDatabaseDocumentation { [CmdletBinding(SupportsShouldProcess = $true, ConfirmImpact = 'None')] [OutputType([PSCustomObject])] param ( [Parameter(Mandatory = $false, Position = 0)] [string]$SqlInstance, [Parameter(Mandatory = $false)] [System.Management.Automation.PSCredential]$SqlCredential, [Parameter(Mandatory = $true)] [string]$Database, [Parameter(Mandatory = $false)] [string]$OutputPath, [Parameter(Mandatory = $false)] [ValidateSet('HTML', 'TXT', 'Both')] [string]$Format = 'Both', [Parameter(Mandatory = $false)] [switch]$IncludeDefinitions, [Parameter(Mandatory = $false)] [switch]$IncludeIndexFragmentation, [Parameter(Mandatory = $false)] [string[]]$ExcludeTable = @(), [Parameter(Mandatory = $false)] [switch]$EnableException ) begin { $functionName = $MyInvocation.MyCommand.Name if (-not $PSBoundParameters.ContainsKey('SqlInstance') -or [string]::IsNullOrWhiteSpace($SqlInstance)) { $SqlInstance = $env:COMPUTERNAME } if (-not $script:dbatoolsAvailable) { $msg = "dbatools-Modul nicht gefunden. Bitte installieren: Install-Module dbatools" Invoke-mssLogging -Message $msg -FunctionName $functionName -Level "ERROR" throw $msg } if (-not $OutputPath) { $OutputPath = Get-mssDefaultOutputPath } Invoke-mssLogging -Message ("Starte " + $functionName + " fuer " + $Database + " auf " + $SqlInstance) -FunctionName $functionName -Level "INFO" } process { try { $connParams = @{ SqlInstance = $SqlInstance SqlCredential = $SqlCredential Database = $Database ErrorAction = 'Stop' } $connMaster = @{ SqlInstance = $SqlInstance SqlCredential = $SqlCredential Database = 'master' ErrorAction = 'Stop' } $stamp = Get-Date -Format 'yyyyMMdd_HHmmss' $safeInst = $SqlInstance -replace '\\', '_' $safeDb = $Database -replace '[^a-zA-Z0-9_]', '_' $baseName = "DbDoc_" + $safeInst + "_" + $safeDb + "_" + $stamp if (-not (Test-Path $OutputPath)) { New-Item -ItemType Directory -Path $OutputPath -Force | Out-Null } # =================================================================== # DATEN SAMMELN # =================================================================== Invoke-mssLogging -Message ("[$Database] Lese Datenbank-Eigenschaften...") -FunctionName $functionName -Level "INFO" # ------------------------------------------------------------------- # 1. Datenbank-Eigenschaften # ------------------------------------------------------------------- $dbPropQuery = @" SELECT d.name AS DbName, d.database_id, d.create_date AS CreateDate, d.recovery_model_desc AS RecoveryModel, d.compatibility_level AS CompatLevel, d.collation_name AS Collation, d.state_desc AS State, d.is_read_only AS IsReadOnly, d.is_auto_shrink_on AS AutoShrink, d.is_auto_update_stats_on AS AutoUpdateStats, d.is_encrypted AS IsTdeEnabled, SUSER_SNAME(d.owner_sid) AS Owner, (SELECT SUM(size) * 8.0 / 1024 FROM sys.master_files mf WHERE mf.database_id = d.database_id AND mf.type = 0) AS DataSizeMB, (SELECT SUM(size) * 8.0 / 1024 FROM sys.master_files mf WHERE mf.database_id = d.database_id AND mf.type = 1) AS LogSizeMB FROM sys.databases d WHERE d.name = N'$($Database -replace "'", "''")' "@ $dbProps = Invoke-DbaQuery @connMaster -Query $dbPropQuery -ErrorAction Stop # Letztes Backup $lastBackupQuery = @" SELECT MAX(backup_finish_date) AS LastFullBackup FROM msdb.dbo.backupset WHERE database_name = N'$($Database -replace "'", "''")' AND type = 'D' AND is_copy_only = 0 "@ $lastBackup = Invoke-DbaQuery @connMaster -Query $lastBackupQuery -ErrorAction SilentlyContinue # ------------------------------------------------------------------- # 2. Dateigruppen und Dateien # ------------------------------------------------------------------- Invoke-mssLogging -Message ("[$Database] Lese Dateigruppen...") -FunctionName $functionName -Level "INFO" $filesQuery = @" SELECT fg.name AS FilegroupName, fg.type_desc AS FilegroupType, fg.is_default AS IsDefault, df.name AS LogicalName, df.physical_name AS PhysicalPath, df.type_desc AS FileType, CAST(df.size * 8.0 / 1024 AS DECIMAL(18,2)) AS SizeMB, CASE df.max_size WHEN -1 THEN 'Unbegrenzt' WHEN 0 THEN 'Kein Wachstum' ELSE CAST(CAST(df.max_size * 8.0 / 1024 AS INT) AS VARCHAR) + ' MB' END AS MaxSize, CASE df.is_percent_growth WHEN 1 THEN CAST(df.growth AS VARCHAR) + '%' ELSE CAST(CAST(df.growth * 8.0 / 1024 AS INT) AS VARCHAR) + ' MB' END AS AutoGrowth FROM sys.database_files df LEFT JOIN sys.filegroups fg ON df.data_space_id = fg.data_space_id ORDER BY fg.name, df.name "@ $dbFiles = Invoke-DbaQuery @connParams -Query $filesQuery -ErrorAction Stop # ------------------------------------------------------------------- # 3. Tabellen mit Spalten, Indizes, Constraints # ------------------------------------------------------------------- Invoke-mssLogging -Message ("[$Database] Lese Tabellen...") -FunctionName $functionName -Level "INFO" $tablesQuery = @" SELECT s.name AS SchemaName, t.name AS TableName, t.object_id, t.create_date AS CreateDate, t.modify_date AS ModifyDate, p.rows AS RowCount, CAST( (SUM(a.total_pages) * 8.0 / 1024) OVER (PARTITION BY t.object_id) AS DECIMAL(18,2)) AS SizeMB FROM sys.tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id INNER JOIN sys.indexes i ON t.object_id = i.object_id AND i.index_id IN (0,1) INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id WHERE t.is_ms_shipped = 0 ORDER BY s.name, t.name "@ $rawTables = Invoke-DbaQuery @connParams -Query $tablesQuery -ErrorAction Stop # Ausschlüsse anwenden $tables = @($rawTables | Where-Object { $tn = $_.TableName $excl = $false foreach ($pat in $ExcludeTable) { if ($tn -like $pat) { $excl = $true; break } } -not $excl }) # Spalten aller Tabellen in einem Query $columnsQuery = @" SELECT t.object_id, c.column_id, c.name AS ColumnName, tp.name AS DataType, CASE WHEN tp.name IN ('char','varchar','nchar','nvarchar','binary','varbinary') THEN CAST(c.max_length AS VARCHAR) + CASE WHEN c.max_length = -1 THEN ' (max)' ELSE '' END WHEN tp.name IN ('decimal','numeric') THEN '(' + CAST(c.precision AS VARCHAR) + ',' + CAST(c.scale AS VARCHAR) + ')' ELSE '' END AS TypeDetail, c.is_nullable AS IsNullable, c.is_identity AS IsIdentity, OBJECT_DEFINITION(c.default_object_id) AS DefaultValue, ep.value AS ExtendedDescription FROM sys.tables t INNER JOIN sys.columns c ON t.object_id = c.object_id INNER JOIN sys.types tp ON c.user_type_id = tp.user_type_id LEFT JOIN sys.extended_properties ep ON ep.major_id = c.object_id AND ep.minor_id = c.column_id AND ep.name = 'MS_Description' AND ep.class = 1 WHERE t.is_ms_shipped = 0 ORDER BY t.object_id, c.column_id "@ $allColumns = Invoke-DbaQuery @connParams -Query $columnsQuery -ErrorAction Stop # Indizes $indexQuery = @" SELECT i.object_id, i.name AS IndexName, i.type_desc AS IndexType, i.is_primary_key AS IsPrimaryKey, i.is_unique AS IsUnique, i.is_disabled AS IsDisabled, fg.name AS FilegroupName, ( SELECT STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY ic.key_ordinal) FROM sys.index_columns ic INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 0 ) AS KeyColumns, ( SELECT STRING_AGG(c.name, ', ') FROM sys.index_columns ic INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 1 ) AS IncludedColumns FROM sys.indexes i INNER JOIN sys.tables t ON i.object_id = t.object_id LEFT JOIN sys.filegroups fg ON i.data_space_id = fg.data_space_id WHERE t.is_ms_shipped = 0 AND i.type > 0 ORDER BY i.object_id, i.index_id "@ # STRING_AGG erst ab SQL 2017 - Fallback für ältere Versionen $indexQueryLegacy = @" SELECT i.object_id, i.name AS IndexName, i.type_desc AS IndexType, i.is_primary_key AS IsPrimaryKey, i.is_unique AS IsUnique, i.is_disabled AS IsDisabled, fg.name AS FilegroupName, STUFF(( SELECT ', ' + c.name FROM sys.index_columns ic2 INNER JOIN sys.columns c ON ic2.object_id = c.object_id AND ic2.column_id = c.column_id WHERE ic2.object_id = i.object_id AND ic2.index_id = i.index_id AND ic2.is_included_column = 0 ORDER BY ic2.key_ordinal FOR XML PATH('') ), 1, 2, '') AS KeyColumns, STUFF(( SELECT ', ' + c.name FROM sys.index_columns ic2 INNER JOIN sys.columns c ON ic2.object_id = c.object_id AND ic2.column_id = c.column_id WHERE ic2.object_id = i.object_id AND ic2.index_id = i.index_id AND ic2.is_included_column = 1 FOR XML PATH('') ), 1, 2, '') AS IncludedColumns FROM sys.indexes i INNER JOIN sys.tables t ON i.object_id = t.object_id LEFT JOIN sys.filegroups fg ON i.data_space_id = fg.data_space_id WHERE t.is_ms_shipped = 0 AND i.type > 0 ORDER BY i.object_id, i.index_id "@ # SQL-Version prüfen für STRING_AGG $versionQuery = "SELECT SERVERPROPERTY('ProductMajorVersion') AS MajorVersion" $versionResult = Invoke-DbaQuery @connMaster -Query $versionQuery -ErrorAction SilentlyContinue $sqlMajorVersion = if ($versionResult) { [int]$versionResult.MajorVersion } else { 13 } $allIndexes = if ($sqlMajorVersion -ge 14) { Invoke-DbaQuery @connParams -Query $indexQuery -ErrorAction SilentlyContinue } else { Invoke-DbaQuery @connParams -Query $indexQueryLegacy -ErrorAction SilentlyContinue } # Fremdschlüssel $fkQuery = @" SELECT fk.parent_object_id AS ObjectId, fk.name AS FkName, OBJECT_SCHEMA_NAME(fk.parent_object_id) AS ParentSchema, OBJECT_NAME(fk.parent_object_id) AS ParentTable, OBJECT_SCHEMA_NAME(fk.referenced_object_id) AS RefSchema, OBJECT_NAME(fk.referenced_object_id) AS RefTable, fk.delete_referential_action_desc AS OnDelete, fk.update_referential_action_desc AS OnUpdate, fk.is_disabled AS IsDisabled, COL_NAME(fkc.parent_object_id, fkc.parent_column_id) AS ParentColumn, COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id) AS RefColumn FROM sys.foreign_keys fk INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id ORDER BY fk.parent_object_id, fk.name, fkc.constraint_column_id "@ $allForeignKeys = Invoke-DbaQuery @connParams -Query $fkQuery -ErrorAction SilentlyContinue # Fragmentierung (optional) $fragLookup = @{ } if ($IncludeIndexFragmentation) { Invoke-mssLogging -Message ("[$Database] Lese Indexfragmentierung (kann dauern)...") -FunctionName $functionName -Level "INFO" $fragQuery = @" SELECT object_id, index_id, ROUND(avg_fragmentation_in_percent, 1) AS FragPercent FROM sys.dm_db_index_physical_stats(DB_ID(N'$($Database -replace "'", "''")', NULL, NULL, NULL, 'LIMITED') WHERE index_id > 0 AND page_count > 100 "@ $fragData = Invoke-DbaQuery @connParams -Query $fragQuery -ErrorAction SilentlyContinue foreach ($fr in @($fragData)) { $fragLookup[$fr.object_id.ToString() + '_' + $fr.index_id.ToString()] = $fr.FragPercent } } # ------------------------------------------------------------------- # 4. Views # ------------------------------------------------------------------- Invoke-mssLogging -Message ("[$Database] Lese Views...") -FunctionName $functionName -Level "INFO" $viewsQuery = @" SELECT s.name AS SchemaName, v.name AS ViewName, v.object_id, v.create_date, v.modify_date, v.is_ms_shipped, c.is_updatable AS IsUpdatable FROM sys.views v INNER JOIN sys.schemas s ON v.schema_id = s.schema_id LEFT JOIN INFORMATION_SCHEMA.VIEWS c ON c.TABLE_SCHEMA = s.name AND c.TABLE_NAME = v.name WHERE v.is_ms_shipped = 0 ORDER BY s.name, v.name "@ $allViews = Invoke-DbaQuery @connParams -Query $viewsQuery -ErrorAction SilentlyContinue # ------------------------------------------------------------------- # 5. Stored Procedures # ------------------------------------------------------------------- Invoke-mssLogging -Message ("[$Database] Lese Stored Procedures...") -FunctionName $functionName -Level "INFO" $procsQuery = @" SELECT s.name AS SchemaName, p.name AS ProcName, p.object_id, p.create_date, p.modify_date, ( SELECT STUFF(( SELECT ', ' + pr.name + ' ' + tp.name + CASE WHEN pr.is_output = 1 THEN ' OUTPUT' ELSE '' END FROM sys.parameters pr INNER JOIN sys.types tp ON pr.user_type_id = tp.user_type_id WHERE pr.object_id = p.object_id AND pr.parameter_id > 0 ORDER BY pr.parameter_id FOR XML PATH('') ), 1, 2, '') ) AS Parameters FROM sys.procedures p INNER JOIN sys.schemas s ON p.schema_id = s.schema_id WHERE p.is_ms_shipped = 0 ORDER BY s.name, p.name "@ $allProcs = Invoke-DbaQuery @connParams -Query $procsQuery -ErrorAction SilentlyContinue # ------------------------------------------------------------------- # 6. Funktionen # ------------------------------------------------------------------- Invoke-mssLogging -Message ("[$Database] Lese Funktionen...") -FunctionName $functionName -Level "INFO" $funcsQuery = @" SELECT s.name AS SchemaName, o.name AS FuncName, o.object_id, o.type_desc AS FuncType, o.create_date, o.modify_date, ( SELECT STUFF(( SELECT ', ' + pr.name + ' ' + tp.name FROM sys.parameters pr INNER JOIN sys.types tp ON pr.user_type_id = tp.user_type_id WHERE pr.object_id = o.object_id AND pr.parameter_id > 0 ORDER BY pr.parameter_id FOR XML PATH('') ), 1, 2, '') ) AS Parameters FROM sys.objects o INNER JOIN sys.schemas s ON o.schema_id = s.schema_id WHERE o.type IN ('FN','IF','TF','FS','FT') AND o.is_ms_shipped = 0 ORDER BY s.name, o.name "@ $allFuncs = Invoke-DbaQuery @connParams -Query $funcsQuery -ErrorAction SilentlyContinue # ------------------------------------------------------------------- # 7. Trigger # ------------------------------------------------------------------- $triggerQuery = @" SELECT tr.name AS TriggerName, s.name AS SchemaName, t.name AS TableName, tr.is_disabled AS IsDisabled, tr.is_instead_of_trigger AS IsInsteadOf, ( SELECT STUFF(( SELECT ', ' + te.type_desc FROM sys.trigger_events te WHERE te.object_id = tr.object_id FOR XML PATH('') ), 1, 2, '') ) AS Events, tr.create_date, tr.modify_date FROM sys.triggers tr INNER JOIN sys.tables t ON tr.parent_id = t.object_id INNER JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE tr.is_ms_shipped = 0 ORDER BY s.name, t.name, tr.name "@ $allTriggers = Invoke-DbaQuery @connParams -Query $triggerQuery -ErrorAction SilentlyContinue # ------------------------------------------------------------------- # 8. Benutzer und Rollen # ------------------------------------------------------------------- $usersQuery = @" SELECT dp.name AS UserName, dp.type_desc AS UserType, dp.create_date, SUSER_SNAME(dp.sid) AS LoginName, dp.default_schema_name AS DefaultSchema, ( SELECT STUFF(( SELECT ', ' + r.name FROM sys.database_role_members drm INNER JOIN sys.database_principals r ON drm.role_principal_id = r.principal_id WHERE drm.member_principal_id = dp.principal_id FOR XML PATH('') ), 1, 2, '') ) AS Roles FROM sys.database_principals dp WHERE dp.type IN ('S','U','G','E') AND dp.name NOT IN ('dbo','guest','INFORMATION_SCHEMA','sys','public') AND dp.is_fixed_role = 0 ORDER BY dp.type_desc, dp.name "@ $allUsers = Invoke-DbaQuery @connParams -Query $usersQuery -ErrorAction SilentlyContinue # =================================================================== # Lookup-Tabellen aufbauen (PS 5.1: Hashtable statt Dictionary) # =================================================================== $columnsByObjId = @{ } foreach ($col in @($allColumns)) { $key = $col.object_id.ToString() if (-not $columnsByObjId.ContainsKey($key)) { $columnsByObjId[$key] = [System.Collections.Generic.List[object]]::new() } $columnsByObjId[$key].Add($col) } $indexesByObjId = @{ } foreach ($idx in @($allIndexes)) { $key = $idx.object_id.ToString() if (-not $indexesByObjId.ContainsKey($key)) { $indexesByObjId[$key] = [System.Collections.Generic.List[object]]::new() } $indexesByObjId[$key].Add($idx) } $fkByObjId = @{ } foreach ($fk in @($allForeignKeys)) { $key = $fk.ObjectId.ToString() if (-not $fkByObjId.ContainsKey($key)) { $fkByObjId[$key] = [System.Collections.Generic.List[object]]::new() } $fkByObjId[$key].Add($fk) } # =================================================================== # AUSGABE GENERIEREN # =================================================================== if ($PSCmdlet.ShouldProcess($Database, "Dokumentation exportieren")) { $txtFile = $null $htmlFile = $null # ------------------------------------------------------------------- # TXT-Ausgabe # ------------------------------------------------------------------- if ($Format -in @('TXT', 'Both')) { Invoke-mssLogging -Message ("[$Database] Erstelle TXT-Bericht...") -FunctionName $functionName -Level "INFO" $txtFile = Join-Path $OutputPath ($baseName + ".txt") $txt = [System.Collections.Generic.List[string]]::new() $sep70 = "=" * 70 $sep40 = "-" * 40 $txt.Add($sep70) $txt.Add(" DATENBANKDOKUMENTATION: " + $Database) $txt.Add(" Instanz : " + $SqlInstance) $txt.Add(" Erstellt : " + (Get-Date -Format 'yyyy-MM-dd HH:mm:ss')) $txt.Add($sep70) $txt.Add("") # DB-Eigenschaften $txt.Add("DATENBANK-EIGENSCHAFTEN") $txt.Add($sep40) if ($dbProps) { $txt.Add(" Recovery-Modell : " + $dbProps.RecoveryModel) $txt.Add(" Kompatibilitaet : " + $dbProps.CompatLevel) $txt.Add(" Collation : " + $dbProps.Collation) $txt.Add(" Status : " + $dbProps.State) $txt.Add(" Eigentümer : " + $dbProps.Owner) $txt.Add(" Erstellt am : " + (Format-mssDate $dbProps.CreateDate)) $txt.Add(" Datengröße : " + [math]::Round($dbProps.DataSizeMB, 2) + " MB") $txt.Add(" Loggröße : " + [math]::Round($dbProps.LogSizeMB, 2) + " MB") $txt.Add(" TDE aktiv : " + $dbProps.IsTdeEnabled) $txt.Add(" Letztes Backup : " + (Format-mssDate (if ($lastBackup) { $lastBackup.LastFullBackup } else { $null }))) } $txt.Add("") # Dateien $txt.Add("DATEIGRUPPEN UND DATEIEN") $txt.Add($sep40) foreach ($f in @($dbFiles)) { $txt.Add(" [" + $f.FilegroupName + "] " + $f.LogicalName) $txt.Add(" Pfad : " + $f.PhysicalPath) $txt.Add(" Größe : " + $f.SizeMB + " MB | MaxSize: " + $f.MaxSize + " | Autogrow: " + $f.AutoGrowth) } $txt.Add("") # Tabellen $txt.Add("TABELLEN (" + $tables.Count + ")") $txt.Add($sep40) foreach ($tbl in @($tables)) { $oid = $tbl.object_id.ToString() $cols = if ($columnsByObjId.ContainsKey($oid)) { $columnsByObjId[$oid] } else { @() } $idxs = if ($indexesByObjId.ContainsKey($oid)) { $indexesByObjId[$oid] } else { @() } $fks = if ($fkByObjId.ContainsKey($oid)) { $fkByObjId[$oid] } else { @() } $txt.Add("") $txt.Add(" [" + $tbl.SchemaName + "].[" + $tbl.TableName + "]") $txt.Add(" Zeilen: " + $tbl.RowCount + " | Größe: " + $tbl.SizeMB + " MB | Geändert: " + (Format-mssDate $tbl.ModifyDate)) # Spalten $txt.Add(" SPALTEN:") foreach ($col in @($cols)) { $nullable = if ($col.IsNullable) { "NULL" } else { "NOT NULL" } $identity = if ($col.IsIdentity) { " IDENTITY" } else { "" } $defVal = if ($col.DefaultValue) { " DEFAULT " + $col.DefaultValue } else { "" } $typeStr = $col.DataType + $col.TypeDetail $txt.Add(" " + $col.ColumnName.PadRight(30) + $typeStr.PadRight(20) + $nullable + $identity + $defVal) } # Indizes if ($idxs.Count -gt 0) { $txt.Add(" INDIZES:") foreach ($idx in @($idxs)) { $pk = if ($idx.IsPrimaryKey) { " [PK]" } else { "" } $uniq = if ($idx.IsUnique) { " [UNIQUE]" } else { "" } $disab = if ($idx.IsDisabled) { " [DISABLED]" } else { "" } $fragStr = "" if ($IncludeIndexFragmentation -and $fragLookup.ContainsKey($oid + '_' + $idx.index_id.ToString())) { $fragStr = " Frag=" + $fragLookup[$oid + '_' + $idx.index_id.ToString()] + "%" } $txt.Add(" " + $idx.IndexName + $pk + $uniq + $disab + $fragStr) $txt.Add(" Key: " + $idx.KeyColumns) if ($idx.IncludedColumns) { $txt.Add(" Inc: " + $idx.IncludedColumns) } } } # Fremdschlüssel if ($fks.Count -gt 0) { $txt.Add(" FREMDSCHLÜSSEL:") $fkGrouped = $fks | Group-Object FkName foreach ($fkGroup in $fkGrouped) { $first = $fkGroup.Group[0] $cols = ($fkGroup.Group | ForEach-Object { $_.ParentColumn }) -join ", " $refs = ($fkGroup.Group | ForEach-Object { $_.RefColumn }) -join ", " $disab = if ($first.IsDisabled) { " [DISABLED]" } else { "" } $txt.Add(" " + $first.FkName + $disab) $txt.Add(" " + $cols + " -> [" + $first.RefSchema + "].[" + $first.RefTable + "] (" + $refs + ")") $txt.Add(" OnDelete=" + $first.OnDelete + " OnUpdate=" + $first.OnUpdate) } } } # Views if ($allViews) { $txt.Add("") $txt.Add("VIEWS (" + @($allViews).Count + ")") $txt.Add($sep40) foreach ($v in @($allViews)) { $txt.Add(" [" + $v.SchemaName + "].[" + $v.ViewName + "] Erstellt: " + (Format-mssDate $v.create_date)) } } # Stored Procedures if ($allProcs) { $txt.Add("") $txt.Add("STORED PROCEDURES (" + @($allProcs).Count + ")") $txt.Add($sep40) foreach ($p in @($allProcs)) { $txt.Add(" [" + $p.SchemaName + "].[" + $p.ProcName + "]") if ($p.Parameters) { $txt.Add(" Parameter: " + $p.Parameters) } } } # Funktionen if ($allFuncs) { $txt.Add("") $txt.Add("FUNKTIONEN (" + @($allFuncs).Count + ")") $txt.Add($sep40) foreach ($f in @($allFuncs)) { $txt.Add(" [" + $f.SchemaName + "].[" + $f.FuncName + "] (" + $f.FuncType + ")") if ($f.Parameters) { $txt.Add(" Parameter: " + $f.Parameters) } } } # Trigger if ($allTriggers) { $txt.Add("") $txt.Add("TRIGGER (" + @($allTriggers).Count + ")") $txt.Add($sep40) foreach ($tr in @($allTriggers)) { $disab = if ($tr.IsDisabled) { " [DISABLED]" } else { "" } $type = if ($tr.IsInsteadOf) { "INSTEAD OF" } else { "AFTER" } $txt.Add(" " + $tr.TriggerName + $disab) $txt.Add(" Tabelle: [" + $tr.SchemaName + "].[" + $tr.TableName + "] Typ: " + $type + " Ereignisse: " + $tr.Events) } } # Benutzer if ($allUsers) { $txt.Add("") $txt.Add("DATENBANKBENUTZER (" + @($allUsers).Count + ")") $txt.Add($sep40) foreach ($u in @($allUsers)) { $login = if ($u.LoginName) { $u.LoginName } else { "(kein Login)" } $roles = if ($u.Roles) { $u.Roles } else { "(keine Rollen)" } $txt.Add(" " + $u.UserName + " [" + $u.UserType + "] -> Login: " + $login) $txt.Add(" Rollen: " + $roles) } } $txt | Out-File -FilePath $txtFile -Encoding UTF8 -Force Invoke-mssLogging -Message ("[$Database] TXT-Bericht: " + $txtFile) -FunctionName $functionName -Level "INFO" } # ------------------------------------------------------------------- # HTML-Ausgabe # ------------------------------------------------------------------- if ($Format -in @('HTML', 'Both')) { Invoke-mssLogging -Message ("[$Database] Erstelle HTML-Bericht...") -FunctionName $functionName -Level "INFO" $htmlFile = Join-Path $OutputPath ($baseName + ".html") $html = Build-mssDatabaseDocHtml ` -Database $Database ` -SqlInstance $SqlInstance ` -DbProps $dbProps ` -LastBackup $lastBackup ` -DbFiles $dbFiles ` -Tables $tables ` -ColumnsByObjId $columnsByObjId ` -IndexesByObjId $indexesByObjId ` -FkByObjId $fkByObjId ` -Views $allViews ` -Procs $allProcs ` -Funcs $allFuncs ` -Triggers $allTriggers ` -Users $allUsers ` -FragLookup $fragLookup ` -IncludeFrag $IncludeIndexFragmentation.IsPresent $html | Out-File -FilePath $htmlFile -Encoding UTF8 -Force Invoke-mssLogging -Message ("[$Database] HTML-Bericht: " + $htmlFile) -FunctionName $functionName -Level "INFO" } Copy-mssToCentralPath -Path @(($txtFile, $htmlFile | Where-Object { $_ })) $result = [PSCustomObject]@{ SqlInstance = $SqlInstance Database = $Database TxtFile = $txtFile HtmlFile = $htmlFile TableCount = $tables.Count ViewCount = @($allViews).Count ProcCount = @($allProcs).Count Success = $true } Write-Host ("Dokumentation erstellt fuer: " + $Database) -ForegroundColor Green if ($htmlFile) { Write-Host ("HTML: " + $htmlFile) -ForegroundColor Cyan } if ($txtFile) { Write-Host ("TXT : " + $txtFile) -ForegroundColor Cyan } return $result } } catch { $errMsg = "Fehler bei Datenbankdokumentation: " + $_.Exception.Message Invoke-mssLogging -Message $errMsg -FunctionName $functionName -Level "ERROR" if ($EnableException) { throw } Write-Error $errMsg return [PSCustomObject]@{ Success = $false; ErrorMessage = $errMsg } } } end { Invoke-mssLogging -Message ($functionName + " abgeschlossen.") -FunctionName $functionName -Level "INFO" } } # --------------------------------------------------------------------------- # Private Hilfsfunktionen # --------------------------------------------------------------------------- function Format-mssDate { param ([object]$Date) if ($null -eq $Date) { return 'n/a' } try { return ([datetime]$Date).ToString('yyyy-MM-dd HH:mm') } catch { return 'n/a' } } function Build-mssDatabaseDocHtml { param ( [string]$Database, [string]$SqlInstance, $DbProps, $LastBackup, $DbFiles, $Tables, [hashtable]$ColumnsByObjId, [hashtable]$IndexesByObjId, [hashtable]$FkByObjId, $Views, $Procs, $Funcs, $Triggers, $Users, [hashtable]$FragLookup, [bool]$IncludeFrag ) $now = Get-Date -Format 'yyyy-MM-dd HH:mm:ss' # Inline-CSS - kein externes Framework nötig $css = @" body{font-family:Segoe UI,Arial,sans-serif;font-size:13px;margin:0;background:#f5f5f5;color:#333} h1{background:#0078d4;color:#fff;padding:16px 24px;margin:0;font-size:20px} h2{background:#005a9e;color:#fff;padding:8px 16px;margin:16px 0 0 0;font-size:15px;cursor:pointer} h2:hover{background:#004d8a} h3{padding:4px 16px;margin:8px 0 2px 0;font-size:13px;color:#005a9e;border-left:3px solid #0078d4} .container{max-width:1400px;margin:0 auto;padding:16px} .section{background:#fff;border:1px solid #ddd;margin-bottom:8px;border-radius:2px} .section-body{padding:8px 16px;display:none} .section-body.open{display:block} table{border-collapse:collapse;width:100%;font-size:12px;margin-bottom:8px} th{background:#0078d4;color:#fff;padding:5px 8px;text-align:left;white-space:nowrap} td{padding:4px 8px;border-bottom:1px solid #eee;vertical-align:top} tr:nth-child(even){background:#f9f9f9} tr:hover{background:#e6f2ff} .badge-pk{background:#d4380d;color:#fff;padding:1px 5px;border-radius:3px;font-size:10px} .badge-idx{background:#096dd9;color:#fff;padding:1px 5px;border-radius:3px;font-size:10px} .badge-fk{background:#389e0d;color:#fff;padding:1px 5px;border-radius:3px;font-size:10px} .badge-dis{background:#999;color:#fff;padding:1px 5px;border-radius:3px;font-size:10px} .frag-ok{color:#389e0d}.frag-warn{color:#d4b106}.frag-crit{color:#cf1322} .summary{display:flex;gap:16px;flex-wrap:wrap;padding:12px 16px} .stat{background:#e6f2ff;border:1px solid #91caff;border-radius:4px;padding:8px 16px;min-width:100px;text-align:center} .stat-n{font-size:22px;font-weight:bold;color:#0078d4} .stat-l{font-size:11px;color:#555} "@ $js = @" function tog(id){var el=document.getElementById(id);el.classList.toggle('open');} "@ $sb = [System.Text.StringBuilder]::new() $null = $sb.Append("<!DOCTYPE html><html lang='de'><head><meta charset='UTF-8'>") $null = $sb.Append("<title>DB-Dokumentation: " + $Database + "</title>") $null = $sb.Append("<style>" + $css + "</style>") $null = $sb.Append("<script>" + $js + "</script></head><body>") $null = $sb.Append("<h1>Datenbankdokumentation: " + $Database + " | " + $SqlInstance + "</h1>") $null = $sb.Append("<div class='container'>") # Zusammenfassung $null = $sb.Append("<div class='summary'>") $null = $sb.Append(Html-Stat $Tables.Count "Tabellen") $null = $sb.Append(Html-Stat @($Views).Count "Views") $null = $sb.Append(Html-Stat @($Procs).Count "Procedures") $null = $sb.Append(Html-Stat @($Funcs).Count "Funktionen") $null = $sb.Append(Html-Stat @($Triggers).Count "Trigger") $null = $sb.Append(Html-Stat @($Users).Count "Benutzer") if ($DbProps) { $null = $sb.Append(Html-Stat ([math]::Round($DbProps.DataSizeMB, 0).ToString() + " MB") "Daten") $null = $sb.Append(Html-Stat ([math]::Round($DbProps.LogSizeMB, 0).ToString() + " MB") "Log") } $null = $sb.Append("<div class='stat'><div class='stat-n' style='font-size:11px'>" + $now + "</div><div class='stat-l'>Erstellt</div></div>") $null = $sb.Append("</div>") # DB-Eigenschaften $null = $sb.Append(Html-Section "props" "Datenbank-Eigenschaften" $true) if ($DbProps) { $lastBk = if ($LastBackup -and $LastBackup.LastFullBackup) { Format-mssDate $LastBackup.LastFullBackup } else { '<span style="color:red">KEIN BACKUP</span>' } $null = $sb.Append("<table>") $null = $sb.Append(Html-PropRow "Recovery-Modell" $DbProps.RecoveryModel) $null = $sb.Append(Html-PropRow "Kompatibilitaet" $DbProps.CompatLevel) $null = $sb.Append(Html-PropRow "Collation" $DbProps.Collation) $null = $sb.Append(Html-PropRow "Status" $DbProps.State) $null = $sb.Append(Html-PropRow "Eigentümer" $DbProps.Owner) $null = $sb.Append(Html-PropRow "Erstellt am" (Format-mssDate $DbProps.CreateDate)) $null = $sb.Append(Html-PropRow "Datengröße" ([math]::Round($DbProps.DataSizeMB, 2).ToString() + " MB")) $null = $sb.Append(Html-PropRow "Loggröße" ([math]::Round($DbProps.LogSizeMB, 2).ToString() + " MB")) $null = $sb.Append(Html-PropRow "TDE aktiviert" $DbProps.IsTdeEnabled) $null = $sb.Append(Html-PropRow "Auto Shrink" $DbProps.AutoShrink) $null = $sb.Append(Html-PropRow "Letztes Backup" $lastBk) $null = $sb.Append("</table>") } $null = $sb.Append("</div></div>") # Dateien $null = $sb.Append(Html-Section "files" ("Dateigruppen & Dateien (" + @($DbFiles).Count + ")") $false) $null = $sb.Append("<table><tr><th>Dateigruppe</th><th>Log.Name</th><th>Typ</th><th>Pfad</th><th>Größe MB</th><th>MaxSize</th><th>Autogrow</th></tr>") foreach ($f in @($DbFiles)) { $fg = if ($f.FilegroupName) { $f.FilegroupName } else { "(Log)" } $null = $sb.Append("<tr><td>" + [System.Web.HttpUtility]::HtmlEncode($fg) + "</td>") $null = $sb.Append("<td>" + [System.Web.HttpUtility]::HtmlEncode($f.LogicalName) + "</td>") $null = $sb.Append("<td>" + $f.FileType + "</td>") $null = $sb.Append("<td style='font-size:11px'>" + [System.Web.HttpUtility]::HtmlEncode($f.PhysicalPath) + "</td>") $null = $sb.Append("<td>" + $f.SizeMB + "</td>") $null = $sb.Append("<td>" + $f.MaxSize + "</td>") $null = $sb.Append("<td>" + $f.AutoGrowth + "</td></tr>") } $null = $sb.Append("</table></div></div>") # Tabellen $null = $sb.Append(Html-Section "tables" ("Tabellen (" + $Tables.Count + ")") $false) foreach ($tbl in @($Tables)) { $oid = $tbl.object_id.ToString() $cols = if ($ColumnsByObjId.ContainsKey($oid)) { $ColumnsByObjId[$oid] } else { @() } $idxs = if ($IndexesByObjId.ContainsKey($oid)) { $IndexesByObjId[$oid] } else { @() } $fks = if ($FkByObjId.ContainsKey($oid)) { $FkByObjId[$oid] } else { @() } $tblKey = "tbl_" + $oid $null = $sb.Append("<h3 onclick=""tog('" + $tblKey + "')"" style='cursor:pointer'>") $null = $sb.Append("[" + $tbl.SchemaName + "].[" + $tbl.TableName + "]") $null = $sb.Append(" <small>" + $tbl.RowCount + " Zeilen | " + $tbl.SizeMB + " MB</small></h3>") $null = $sb.Append("<div id='" + $tblKey + "' class='section-body'>") # Spalten $null = $sb.Append("<table><tr><th>#</th><th>Spalte</th><th>Typ</th><th>Nullable</th><th>Identity</th><th>Default</th><th>Beschreibung</th></tr>") foreach ($col in @($cols)) { $null = $sb.Append("<tr><td>" + $col.column_id + "</td>") $null = $sb.Append("<td><b>" + [System.Web.HttpUtility]::HtmlEncode($col.ColumnName) + "</b></td>") $null = $sb.Append("<td>" + $col.DataType + [System.Web.HttpUtility]::HtmlEncode($col.TypeDetail) + "</td>") $null = $sb.Append("<td>" + (if ($col.IsNullable) { "YES" } else { "<b>NO</b>" }) + "</td>") $null = $sb.Append("<td>" + (if ($col.IsIdentity) { "YES" } else { "" }) + "</td>") $defEnc = if ($col.DefaultValue) { [System.Web.HttpUtility]::HtmlEncode($col.DefaultValue) } else { "" } $null = $sb.Append("<td><code>" + $defEnc + "</code></td>") $descEnc = if ($col.ExtendedDescription) { [System.Web.HttpUtility]::HtmlEncode($col.ExtendedDescription) } else { "" } $null = $sb.Append("<td>" + $descEnc + "</td></tr>") } $null = $sb.Append("</table>") # Indizes if (@($idxs).Count -gt 0) { $null = $sb.Append("<table><tr><th>Index</th><th>Typ</th><th>Key-Spalten</th><th>Include</th><th>Status</th>") if ($IncludeFrag) { $null = $sb.Append("<th>Frag%</th>") } $null = $sb.Append("</tr>") foreach ($idx in @($idxs)) { $badge = if ($idx.IsPrimaryKey) { "<span class='badge-pk'>PK</span>" } else { "<span class='badge-idx'>IDX</span>" } if ($idx.IsUnique) { $badge += " <span class='badge-idx'>UNIQUE</span>" } if ($idx.IsDisabled) { $badge += " <span class='badge-dis'>DISABLED</span>" } $null = $sb.Append("<tr><td>" + [System.Web.HttpUtility]::HtmlEncode($idx.IndexName) + "</td>") $null = $sb.Append("<td>" + $idx.IndexType + "</td>") $null = $sb.Append("<td>" + [System.Web.HttpUtility]::HtmlEncode($idx.KeyColumns) + "</td>") $incEnc = if ($idx.IncludedColumns) { [System.Web.HttpUtility]::HtmlEncode($idx.IncludedColumns) } else { "" } $null = $sb.Append("<td>" + $incEnc + "</td>") $null = $sb.Append("<td>" + $badge + "</td>") if ($IncludeFrag) { $fk = $oid + '_' + $idx.index_id.ToString() $pct = if ($FragLookup.ContainsKey($fk)) { $FragLookup[$fk] } else { $null } if ($null -ne $pct) { $cls = if ($pct -ge 30) { "frag-crit" } elseif ($pct -ge 10) { "frag-warn" } else { "frag-ok" } $null = $sb.Append("<td class='" + $cls + "'>" + $pct + "%</td>") } else { $null = $sb.Append("<td></td>") } } $null = $sb.Append("</tr>") } $null = $sb.Append("</table>") } # Fremdschlüssel if (@($fks).Count -gt 0) { $null = $sb.Append("<table><tr><th>FK-Name</th><th>Spalten</th><th>Referenz-Tabelle</th><th>Ref-Spalten</th><th>OnDelete</th><th>OnUpdate</th></tr>") $fkGrouped = $fks | Group-Object FkName foreach ($fkGroup in $fkGrouped) { $first = $fkGroup.Group[0] $pCols = ($fkGroup.Group | ForEach-Object { $_.ParentColumn }) -join ", " $rCols = ($fkGroup.Group | ForEach-Object { $_.RefColumn }) -join ", " $disab = if ($first.IsDisabled) { " <span class='badge-dis'>DISABLED</span>" } else { "" } $null = $sb.Append("<tr><td><span class='badge-fk'>FK</span> " + [System.Web.HttpUtility]::HtmlEncode($first.FkName) + $disab + "</td>") $null = $sb.Append("<td>" + [System.Web.HttpUtility]::HtmlEncode($pCols) + "</td>") $null = $sb.Append("<td>[" + $first.RefSchema + "].[" + $first.RefTable + "]</td>") $null = $sb.Append("<td>" + [System.Web.HttpUtility]::HtmlEncode($rCols) + "</td>") $null = $sb.Append("<td>" + $first.OnDelete + "</td>") $null = $sb.Append("<td>" + $first.OnUpdate + "</td></tr>") } $null = $sb.Append("</table>") } $null = $sb.Append("</div>") } $null = $sb.Append("</div></div>") # Views $null = $sb.Append(Html-Section "views" ("Views (" + @($Views).Count + ")") $false) $null = $sb.Append("<table><tr><th>Schema</th><th>Name</th><th>Erstellt</th><th>Geändert</th><th>Updatable</th></tr>") foreach ($v in @($Views)) { $null = $sb.Append("<tr><td>" + $v.SchemaName + "</td><td>" + [System.Web.HttpUtility]::HtmlEncode($v.ViewName) + "</td>") $null = $sb.Append("<td>" + (Format-mssDate $v.create_date) + "</td>") $null = $sb.Append("<td>" + (Format-mssDate $v.modify_date) + "</td>") $null = $sb.Append("<td>" + $v.IsUpdatable + "</td></tr>") } $null = $sb.Append("</table></div></div>") # Stored Procedures $null = $sb.Append(Html-Section "procs" ("Stored Procedures (" + @($Procs).Count + ")") $false) $null = $sb.Append("<table><tr><th>Schema</th><th>Name</th><th>Erstellt</th><th>Geändert</th><th>Parameter</th></tr>") foreach ($p in @($Procs)) { $parEnc = if ($p.Parameters) { [System.Web.HttpUtility]::HtmlEncode($p.Parameters) } else { "" } $null = $sb.Append("<tr><td>" + $p.SchemaName + "</td><td>" + [System.Web.HttpUtility]::HtmlEncode($p.ProcName) + "</td>") $null = $sb.Append("<td>" + (Format-mssDate $p.create_date) + "</td>") $null = $sb.Append("<td>" + (Format-mssDate $p.modify_date) + "</td>") $null = $sb.Append("<td><code>" + $parEnc + "</code></td></tr>") } $null = $sb.Append("</table></div></div>") # Funktionen $null = $sb.Append(Html-Section "funcs" ("Funktionen (" + @($Funcs).Count + ")") $false) $null = $sb.Append("<table><tr><th>Schema</th><th>Name</th><th>Typ</th><th>Erstellt</th><th>Parameter</th></tr>") foreach ($f in @($Funcs)) { $parEnc = if ($f.Parameters) { [System.Web.HttpUtility]::HtmlEncode($f.Parameters) } else { "" } $null = $sb.Append("<tr><td>" + $f.SchemaName + "</td><td>" + [System.Web.HttpUtility]::HtmlEncode($f.FuncName) + "</td>") $null = $sb.Append("<td>" + $f.FuncType + "</td>") $null = $sb.Append("<td>" + (Format-mssDate $f.create_date) + "</td>") $null = $sb.Append("<td><code>" + $parEnc + "</code></td></tr>") } $null = $sb.Append("</table></div></div>") # Trigger $null = $sb.Append(Html-Section "triggers" ("Trigger (" + @($Triggers).Count + ")") $false) $null = $sb.Append("<table><tr><th>Name</th><th>Tabelle</th><th>Typ</th><th>Ereignisse</th><th>Status</th><th>Erstellt</th></tr>") foreach ($tr in @($Triggers)) { $type = if ($tr.IsInsteadOf) { "INSTEAD OF" } else { "AFTER" } $disab = if ($tr.IsDisabled) { "<span class='badge-dis'>DISABLED</span>" } else { "aktiv" } $null = $sb.Append("<tr><td>" + [System.Web.HttpUtility]::HtmlEncode($tr.TriggerName) + "</td>") $null = $sb.Append("<td>[" + $tr.SchemaName + "].[" + $tr.TableName + "]</td>") $null = $sb.Append("<td>" + $type + "</td><td>" + $tr.Events + "</td>") $null = $sb.Append("<td>" + $disab + "</td>") $null = $sb.Append("<td>" + (Format-mssDate $tr.create_date) + "</td></tr>") } $null = $sb.Append("</table></div></div>") # Benutzer $null = $sb.Append(Html-Section "users" ("Datenbankbenutzer (" + @($Users).Count + ")") $false) $null = $sb.Append("<table><tr><th>Benutzer</th><th>Typ</th><th>Login</th><th>Default Schema</th><th>Rollen</th></tr>") foreach ($u in @($Users)) { $login = if ($u.LoginName) { $u.LoginName } else { "<i>(kein Login)</i>" } $schema = if ($u.DefaultSchema) { $u.DefaultSchema } else { "" } $roles = if ($u.Roles) { $u.Roles } else { "" } $null = $sb.Append("<tr><td>" + [System.Web.HttpUtility]::HtmlEncode($u.UserName) + "</td>") $null = $sb.Append("<td>" + $u.UserType + "</td>") $null = $sb.Append("<td>" + $login + "</td>") $null = $sb.Append("<td>" + $schema + "</td>") $null = $sb.Append("<td>" + [System.Web.HttpUtility]::HtmlEncode($roles) + "</td></tr>") } $null = $sb.Append("</table></div></div>") $null = $sb.Append("</div></body></html>") return $sb.ToString() } function Html-Section { param ([string]$Id, [string]$Title, [bool]$Open) $openClass = if ($Open) { " open" } else { "" } return "<div class='section'><h2 onclick=""tog('" + $Id + "-body')"">▼ " + $Title + "</h2><div id='" + $Id + "-body' class='section-body" + $openClass + "'>" } function Html-PropRow { param ([string]$Label, [string]$Value) return "<tr><td style='width:200px;font-weight:bold'>" + $Label + "</td><td>" + $Value + "</td></tr>" } function Html-Stat { param ([object]$Number, [string]$Label) return "<div class='stat'><div class='stat-n'>" + $Number + "</div><div class='stat-l'>" + $Label + "</div></div>" } |