How to generate the script of schema compare result in SQL Database Project

Question

I am using SQL Server database project. Once I compare the schema I want to generate the updated changes script which I will run on Production Database to update the latest table schema and Stored procedure and function etc.

For example:

CASE 1: Employee table is altered

CASE 2: The stored procedure spGetEmployeeDetails is modified

Answer 1

You can edit the project file to set the ScriptDropsFirst attribute of the PreSource element of the automatically generated script to True to redeploy a database by using automatically generated scripts, just refer to http://msdn.microsoft.com/en-us/library/dd465343(v=vs.100).aspx and http://msdn.microsoft.com/en-us/library/ff398069(v=vs.100).aspx. But this method may cause data loss, as it simply drops & recreates the database objects. If you don't care about data loss, you can do it like this.

Also, I will provide you another method. Just use SQL->Schema Compare, set the source database and target database, and click compare, then update will become available. You can click on it to apply database changes or click Generate Script to get the T-SQL script, then add the script to in the Database scripts section in Package/Publish SQL will apply database changes after next publish.