Visual Studio Schema Compare to generate ALTER TABLE sql instead of using temp tables

Question

I'm testing the upgrade of my PHP CMS web application from an old version (version 1) to the new version (version 2). It uses a SQL Server 2008 R2 database.

As part of my testing and documentation process I need to get a list of all the changes that will be made to the database structure during the upgrade. To do that I'm using Microsoft Visual Studio 2015 Community Edition to compare and "diff" the two versions of the database. Visual Studio gives me a list of all the changes that are made to the database tables during the upgrade. That's great, but there's something I don't like.

When a column has been added to a table in the new version of the database it seems that the Visual Studio 2015 schema compare tool generates the sql statements for the addition of the new column this way:

1. It creates a temporary table: CREATE TABLE [dbo].[tmp_ms_xx_users]
2. It inserts the data from the old table into the temp table: INSERT INTO [dbo].[tmp_ms_xx_users] SELECT... FROM [dbo].[users]
3. Then it DROPS the origional table: DROP TABLE [dbo].[users]
4. And renames the temp table to the same name as the origional table. EXECUTE sp_rename N'[dbo].[tmp_ms_xx_users]', N'users'

The [dbo].[users] table now has all the origional data and the new columns. That's fine, but I don't like the use of the temp tables and DROP TABLE statements for my documentation.

Is there a way to get Visual Studio to generate the table change statements as

ALTER TABLE [dbo].[users] ADD [a_new_column] ... or

ALTER TABLE [dbo].[users] DROP COLUMN [old_column] ...

statements instead of using temp tables?

That will make my documentation much better. (I'm only doing this for documentation purposes. The app installer will actually handle all the database changes during the upgrade.)

Thanks.

Verticillate

but I don't like the use of the temp tables and DROP TABLE statements for my documentation. If you see generated script you could see that it is wrapped intransaction. There is nothing to fear about DROP TABLE – Iaverne

Answer 1

Struggled with the same problem.

The option helped when comparing two schemes - ignore column order. Perhaps not only this option affects this behavior, but the choice of the table update model (CREATE or ALTER) definitely depends on it.

The screenshot shows all my options, under which I received the ALTER operator:

Ackler