SSDT - How to change database object names per build configuration

Question

The problem:

I want to manage my database(s) using one SSDT database project. in which I want to centralize database development and automate deployments (mainly stored procedures). In a multi-tenant environment, where object names are preceded by company names, example :

[dbo].[spu_COMPANY_NAME$Stored Procedure Name]

we have a central database in which we do our development and every time we publish, we do a 'Replace All' to the company name.

The SQLCMD variables won't do because they cannot be included inside object names. Is there a way I can build so that for every build configuration I get tailored stored procedures during build/publish, I get a folder structure like this :

--Database.Project/
  --bin/
     --CompanyA(build.companyA.congig)/
     --CompanyB(build.companyB.congig)/

Answer 1

As @PeterSchott mentioned in a previous comment, the solution is easily doable through Powershell scripts and Pre/Post build events in Visual Studio.

I'll post the answer here in case someone needed it for reference (or maybe in case I needed it)

---

First:

Prebuild powershell script, this one takes the project directory, and replaces all strings with 'COMPANY_NAME' to the actual company name depending on the build configuration, for all files with .sql extension in them.

Next:

The powershell script:

if ($args[0] -eq "Release"){
    $company_name = "ReleaseCorp";
}

$dboDir = $args[1] + 'dbo\';

$sqlFiles = Get-ChildItem $dboDir -rec | Where-Object {($_.Extension -eq ".sql")}

foreach ($file in $sqlFiles)
{
    echo $file;
    (Get-Content $file.PSPath) |
    Foreach-Object { $_ -replace "COMPANY_NAME", $company_name } |
    Set-Content $file.PSPath
}

Post-build event does the inverse, only that this time it takes the actual company name for the configuration and puts it back to the token, for upcoming builds.

Worth noting that for Visual Studio to be able to run Powershell (x86) scripts, the following step is mandatory :

Run Windows Powershell (x86) in administrator mode, and run :

set-executionpolicy unrestricted

More info about this to be found in David Frette's informative blog post : Creating Powershell pre-build and post-build events for Visual Studio projects