Snippet Name: SQL_Server_Doc_Automation

Tags:

Created Date: 2025-06-16 13:12:38
Last Modified Date: 2025-05-31 10:32:02

Dependencies:

DBA Notes:

Sample Output:

Title: SQL Server Doc Automation
Author: user
in SQL server, i have a stored procedure named [dbo].[sp_dbDocumentation]. it returns multiple rows, and the data is actually all the lines of an html document describing the current database. help me write a powershell that loops through each database, and takes the results of the data table and puts it into a document that is named $databasename.html, for each database

Author: ChatGPT
Here???s a PowerShell script that connects to your SQL Server, loops through each database, executes the `[dbo].[sp_dbDocumentation]` stored procedure, and writes the resulting rows into an HTML file named `$DatabaseName.html`.

### Script

```powershell
# Load the required SQL Server module
Import-Module SqlServer

# Define SQL Server connection variables
$ServerName = "YourServerName"  # Replace with your SQL Server name
$OutputFolder = "C:\DatabaseDocumentation"  # Folder to save the HTML files

# Ensure the output folder exists
if (!(Test-Path -Path $OutputFolder)) {
    New-Item -ItemType Directory -Path $OutputFolder
}

# Define the SQL query to retrieve database names
$DatabaseQuery = @"
SELECT name
FROM sys.databases
WHERE state_desc = 'ONLINE'
  AND name NOT IN ('master', 'tempdb', 'model', 'msdb'); -- Exclude system databases
"@

# Get the list of databases
$Databases = Invoke-Sqlcmd -ServerInstance $ServerName -Query $DatabaseQuery

# Loop through each database
foreach ($Database in $Databases) {
    $DatabaseName = $Database.name
    Write-Host "Processing database: $DatabaseName"

    # Query the stored procedure for the current database
    $Query = "EXEC [$DatabaseName].[dbo].[sp_dbDocumentation];"
    $Result = Invoke-Sqlcmd -ServerInstance $ServerName -Database $DatabaseName -Query $Query

    if ($Result) {
        # Combine the rows into an HTML document
        $HTMLContent = $Result | ForEach-Object { $_.YourColumnName } -join "`n"

        # Save the content to an HTML file
        $FilePath = Join-Path -Path $OutputFolder -ChildPath "$DatabaseName.html"
        Set-Content -Path $FilePath -Value $HTMLContent -Encoding UTF8
        Write-Host "Saved documentation to $FilePath"
    } else {
        Write-Host "No documentation found for $DatabaseName."
    }
}
```

### Key Points
1. **SQL Server Module**: Ensure you have the `SqlServer` PowerShell module installed (`Install-Module SqlServer`).
2. **Stored Procedure Results**: Replace `YourColumnName` in `$_YourColumnName` with the column name from the procedure's output.
3. **Excluded Databases**: Modify the `WHERE` clause in the `$DatabaseQuery` if you want to include/exclude additional databases.
4. **Output Folder**: Update `$OutputFolder` to your preferred location for saving the `.html` files.
5. **HTML Content**: If the procedure returns multiple rows, the script concatenates them using a newline character (`-join "\n"`).

Run this script on a machine with appropriate access to the SQL Server and PowerShell configured for SQL Server interaction. Let me know if you need adjustments!