Snippet Name: Powershell_SQL_Script_Execution

Tags:

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

Dependencies:

DBA Notes:

Sample Output:

Title: Powershell SQL Script Execution
Author: user
i have a folder named "C:\_Data\CIG_Report", that contains multiple folders, each with multiple SQL scripts that need to be executed on the server vmprodwdb002.cigfinancial.com,1433 in the database "CIG_Report_Test" . can you help me write a powershell that will execute all the *.sql scripts in the folders "StoredProcedures","Functions", and "Views"

Author: ChatGPT
Sure! Below is a PowerShell script that will recursively find and execute all `*.sql` scripts in the `StoredProcedures`, `Functions`, and `Views` folders within `C:\_Data\CIG_Report`. The script uses `Invoke-Sqlcmd` to execute the SQL scripts against `vmprodwdb002.cigfinancial.com,1433` on the `CIG_Report_Test` database.

---

### **PowerShell Script**
```powershell
# Define SQL Server connection details
$SqlServer = "vmprodwdb002.cigfinancial.com,1433"
$Database = "CIG_Report_Test"

# Root folder containing the SQL scripts
$RootFolder = "C:\_Data\CIG_Report"

# Define target folders
$TargetFolders = @("StoredProcedures", "Functions", "Views")

# Iterate over each target folder
foreach ($Folder in $TargetFolders) {
    # Construct the full path
    $FullPath = Join-Path -Path $RootFolder -ChildPath $Folder

    # Check if the folder exists
    if (Test-Path $FullPath) {
        # Get all .sql files in the folder and its subdirectories
        $SqlFiles = Get-ChildItem -Path $FullPath -Filter "*.sql" -Recurse

        foreach ($SqlFile in $SqlFiles) {
            Write-Host "Executing: $($SqlFile.FullName)"

            # Execute the SQL script
            try {
                Invoke-Sqlcmd -ServerInstance $SqlServer -Database $Database -InputFile $SqlFile.FullName -ErrorAction Stop
                Write-Host "Success: $($SqlFile.FullName)" -ForegroundColor Green
            } catch {
                Write-Host "Error executing: $($SqlFile.FullName)" -ForegroundColor Red
                Write-Host $_.Exception.Message -ForegroundColor Yellow
            }
        }
    } else {
        Write-Host "Skipping: $FullPath (Folder does not exist)" -ForegroundColor DarkYellow
    }
}

Write-Host "Execution completed!"
```

---

### **How It Works**
1. It defines the SQL Server connection details (`ServerInstance`, `Database`).
2. It iterates through the folders `StoredProcedures`, `Functions`, and `Views` within `C:\_Data\CIG_Report`.
3. It retrieves all `.sql` files recursively from these folders.
4. It executes each SQL script using `Invoke-Sqlcmd`.
5. It handles errors gracefully and logs successes and failures.

---

### **Prerequisites**
- Ensure `SqlServer` module is installed (`Install-Module -Name SqlServer` if necessary).
- Run PowerShell as Administrator.
- Make sure `Invoke-Sqlcmd` is allowed to run (`Set-ExecutionPolicy RemoteSigned` if needed).

---

### **Logging & Error Handling**
- If a script runs successfully, it's logged in green.
- If an error occurs, the script prints the error message in red/yellow.
- If a folder is missing, it gets skipped with a warning.

Let me know if you need any modifications! ????