CODEWITHSUNDEEP
sql-servervisual-studiovisual-studio-2013sql-server-data-tools
Alex
Alex

Reputation: 822

Add custom SQL statements in Visual Studio database schema update script

We use Visual Studio SQL Server Data Tools to work with a database project.

When you rename any stored procedure or any object name or definition using the refactor tool, SSDT automatically uses the RefactorLog to handle this change which is pretty awesome.

But I was wondering if it possible to add some SQL statements to the project in order to do some customized task whenever the database schema is updated. For instance:

Nowadays, we have manage this tasks manually: whenever a database is updated, we have to modify the upgrade scripts with custom data updates. Everything very manual which is very dangerous!!

Maybe can be done in Visual Studio with SSDT or any other plugin...

Lets suppose a table of People like:

People:
  Id
  Name     
  Gender
  ...

But we want to do something VERY SILLY as renaming the column Gender to Title:

People:
  Id
  Name
  Title
  ...

Whenever a database is updated, Visual Studio + SSDT will check the RefactorLog table to find out which refactor instructions are missing and automatically will execute them renaming the column, and the data will be kept ... all very nice :)

But also we want to replace the values from the old Gender column: 'M'/'W' with new values according to the new Title column: 'Mr'/'Ms'. Something like:

UPDATE People
  SET Title = CASE Title WHEN 'M' THEN 'Mister' ELSE 'Miss' END

Is there any way to make Visual Studio include this custom statement in the update schema script?

Can be "bound to the refactoring statement" or done chronically (in my example after the refactor statement)?

Upvotes: 4

Views: 1255

Answers (1)

Keith
Keith

Reputation: 21224

You can handle this via a post-deployment script. These scripts are run after all other table/etc. changes from your project are made. Thus you can't ensure your custom code will run immediately after the refactor, but it will happen very soon after.

One caveat to this approach: Once your custom code is run, you will need to remove it from the post-deployment script before your next deployment, otherwise it will be run again during your next deployment. Re-running the code is probably not a problem for the update statement you provided but it definitely could be a problem in other scenarios.

Upvotes: 2

Related Questions