I am testing updating my PHP CMS web application from the old version (version 1) to the new version (version 2). It uses the SQL Server 2008 R2 database.
As part of the 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 this, I use Microsoft Visual Studio 2015 Community Edition to compare and "distinguish" the two versions of the database. Visual Studio gives me a list of all the changes made to the database tables during the upgrade. It's great, but I don't like something.
When the column was added to the table in the new version of the database, it seems that the Visual Studio 2015 schema comparison tool generates SQL commands to add the new column as follows:
- Creates a temporary table:
CREATE TABLE [dbo].[tmp_ms_xx_users] - Inserts data from the old table into the temp table:
INSERT INTO [dbo].[tmp_ms_xx_users]
SELECT... FROM [dbo].[users] - Then it resets the original table:
DROP TABLE [dbo].[users] - And rename the tempo table with the same name as the original table.
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_users]', N'users'
The table [dbo].[users]now has all the source data and new columns. This is good, but I don't like using temporary tables and DROP TABLE statements for my documentation.
Is there a way to get Visual Studio to generate table change statements like
ALTER TABLE [dbo].[users] ADD [a_new_column] ... or
ALTER TABLE [dbo].[users] DROP COLUMN [old_column] ...
instead of using temporary tables?
. ( . .)
.