SQL Server structure change scripts

I've been doing re-engineering work of a project for the past couple of months. It's bit difficult to do re-engineering work rather than doing sometihng from the scratch because you'll have a hell of lot of limitations while doing re-engineering work.

These re-engineering work included some database changes like adding relationshipes among tables, changing datatypes, lengths of existing columns. Since the project is in the production enviornment I wanted to do these changes without effecting or dropping existing data in the database. I've used couple of tools.

When you are doing changed to the database objects. As an example when you are changing the datatype of an existing column you can generate the alter script for that change. it will create the alter script without effecting your data.

Step 1 : Changing the datatype of the column RegisteredDate from datetime to smalldatetime

Step 2 : Before saving the table design, right clink on a column and click on Generate Change Script...

Step 3 : You'll see the change script on a popup window

Analyze the genereted script

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/

BEGIN TRANSACTION

SET QUOTED_IDENTIFIER ON

SET ARITHABORT ON

SET NUMERIC_ROUNDABORT OFF

SET CONCAT_NULL_YIELDS_NULL ON

SET ANSI_NULLS ON

SET ANSI_PADDING ON

SET ANSI_WARNINGS ON

COMMIT

BEGIN TRANSACTION

GO

CREATE TABLE dbo.Tmp_Customer

(

Id int NOT NULL,

FirstName varchar(50) NOT NULL,

LastName varchar(50) NULL,

WorkPhone varchar(50) NULL,

MobilePhone varchar(50) NULL,

RegisteredDate smalldatetime NOT NULL

) ON [PRIMARY]

GO

IF EXISTS(SELECT * FROM dbo.Customer)

EXEC('INSERT INTO dbo.Tmp_Customer (Id, FirstName, LastName, WorkPhone, MobilePhone, RegisteredDate)

SELECT Id, FirstName, LastName, WorkPhone, MobilePhone, CONVERT(smalldatetime, RegisteredDate) FROM dbo.Customer WITH (HOLDLOCK TABLOCKX)')

GO

DROP TABLE dbo.Customer

GO

EXECUTE sp_rename N'dbo.Tmp_Customer', N'Customer', 'OBJECT'

GO

ALTER TABLE dbo.Customer ADD CONSTRAINT

PK_Customer PRIMARY KEY CLUSTERED

(

Id

) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

COMMIT

If you analyse the generated script, there are 4 major operations.

1. create a temporary table with the new column definitions.

2. Inserts data to the temporary table from the existing table.

3. Drop the existing table

4. Rename the new/temporary table to the name of the existing table.

So you won't lose any data.

This is a feature provided by SQL Server but you can download this database publishing tool as a separate software.