String replacement in SQLCMD

Question

I'm trying to set up a test environment that simulates (very roughly) having three servers. I'm doing this by creating 3 databases on my local instance of SQL which I'll call [A], [B], and [C]. Why I can't simply set up three test instances, is a story for another time. I've got a bunch of code files I want to execute using SQLCMD, which is no problem. I've got a parent SQLCMD file that looks like

:Setvar db gt
:Setvar filepath "C:\Documents\test\"
:r "C:\Documents\test\master.sql"

then another file (master.sql) with a few statements like create database $(db) and so on.

The problem now however is in the actual code files, the database referenced is [Alerts] on the actual server, but in my text harness the database has to be [A], [B] or [C] (since I'm using the database names to simulate the server).

Imagine I also have a file MyTable.sql which looks roughly like this:

create table [alerts].[dbo].[MyTable]
(
 id int identity(1,1)
)

In pseudocode, I'd like to do something like this:

(:r $(filepath)MyTable.sql).Replace('[Alerts]', '$(db)')

Does anyone know if this is possible?

Answer 1

If I understand your question correctly, let me try providing an alternate solution.

You could write a quick powershell script to automate the whole process - -reading source .sql files In a loop - -replacing 'Alerts' word with A (B and C). -run those files calling sqlcmd within the powershell script

• Erik Reasonable Rates Darling: "quick PowerShell script"
• Xedni: @DBAalwaysOn I would certainly consider that, in fact I kind of did, but my PoSh knowledge is almost non-existent. Can you help with what that might look like?

Answer 2

As an example something that i have used for an automatic azure upload using PowerShell:

foreach ($file in $configFiles)
{
 write-Host "Processing $($file.PSPath)" -ForegroundColor CYAN
 (Get-Content $file.PSPath) | Foreach-Object { $_ -replace [regex]::escape("ReplaceMe"), "Replaced" } | Set-Content $file.PSPath
 Invoke-SQLCMD -InputFile $file.PSPath -ServerInstance $ServerAddress -database $databaseName 
}

In your case you would just have to run this in another loop for each databaseName ( i would choose something other than A B C ) so something like this:

$dbNames = "databaseA", "databaseB", "databaseC"
$configFiles = Get-ChildItem "path to folder" -filter *.sql
foreach ($item in $dbNames) {
 foreach ($file in $configFiles)
 {
 write-Host "Processing $($file.PSPath)" -ForegroundColor CYAN
 (Get-Content $file.PSPath) | Foreach-Object { $_ -replace [regex]::escape("ReplaceMe"), "$databaseA" } | Set-Content $file.PSPath ## Replace to database name
 Invoke-SQLCMD -InputFile $file.PSPath -ServerInstance $ServerAddress
 (Get-Content $file.PSPath) | Foreach-Object { $_ -replace [regex]::escape("$databaseA"), "ReplaceMe" } | Set-Content $file.PSPath ## Reset to original
 }
}

Hope this helps