creating change scripts in SQL Server Management Studio

when you change the schema of a database, Management Studio makes it easy to create your change script.

first make your change.
then click the generate script button





now you can save it to a sql script file.



this is what the file looks like. notice when you reorder the columns it creates a temporary table copies all the data. drops the original table, then creates the new table with the right column order. the copies the data back to the new table.

/*

   Thursday, October 30, 20088:49:47 AM

   User: 

   Server: stpdev

   Database: ODFL

   Application: 

*/

/* 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

ALTER TABLE dbo.tblEmailType

    DROP CONSTRAINT DF_tblEmailType_LeadDays

GO

ALTER TABLE dbo.tblEmailType

    DROP CONSTRAINT DF_tblEmailType_SendToNonRegistered

GO

ALTER TABLE dbo.tblEmailType

    DROP CONSTRAINT DF_tblEmailType_PostEvent

GO

ALTER TABLE dbo.tblEmailType

    DROP CONSTRAINT DF_tblEmailType_active

GO

ALTER TABLE dbo.tblEmailType

    DROP CONSTRAINT DF_tblEmailType_time_stamp

GO

CREATE TABLE dbo.Tmp_tblEmailType

    (

    EmailTypeID int NOT NULL IDENTITY (1, 1),

    Name nvarchar(50) NOT NULL,

    LeadDays int NULL,

    ExactTargetTemplate nvarchar(255) NULL,

    SendToRegistered bit NOT NULL,

    PostEvent bit NOT NULL,

    active bit NULL,

    MyNewColumn nchar(10) NULL,

    time_stamp smalldatetime NULL

    )  ON [PRIMARY]

GO

ALTER TABLE dbo.Tmp_tblEmailType ADD CONSTRAINT

    DF_tblEmailType_LeadDays DEFAULT ((0)) FOR LeadDays

GO

ALTER TABLE dbo.Tmp_tblEmailType ADD CONSTRAINT

    DF_tblEmailType_SendToNonRegistered DEFAULT ((0)) FOR SendToRegistered

GO

ALTER TABLE dbo.Tmp_tblEmailType ADD CONSTRAINT

    DF_tblEmailType_PostEvent DEFAULT ((0)) FOR PostEvent

GO

ALTER TABLE dbo.Tmp_tblEmailType ADD CONSTRAINT

    DF_tblEmailType_active DEFAULT ((1)) FOR active

GO

ALTER TABLE dbo.Tmp_tblEmailType ADD CONSTRAINT

    DF_tblEmailType_time_stamp DEFAULT (getdate()) FOR time_stamp

GO

SET IDENTITY_INSERT dbo.Tmp_tblEmailType ON

GO

IF EXISTS(SELECT * FROM dbo.tblEmailType)

     EXEC('INSERT INTO dbo.Tmp_tblEmailType (EmailTypeID, Name, LeadDays, ExactTargetTemplate, SendToRegistered, PostEvent, active, time_stamp)

        SELECT EmailTypeID, Name, LeadDays, ExactTargetTemplate, SendToRegistered, PostEvent, active, time_stamp FROM dbo.tblEmailType WITH (HOLDLOCK TABLOCKX)')

GO

SET IDENTITY_INSERT dbo.Tmp_tblEmailType OFF

GO

ALTER TABLE dbo.tblUserEventEmail

    DROP CONSTRAINT FK_tblUserEventEmail_tblEmailType

GO

DROP TABLE dbo.tblEmailType

GO

EXECUTE sp_rename N'dbo.Tmp_tblEmailType', N'tblEmailType', 'OBJECT' 

GO

ALTER TABLE dbo.tblEmailType ADD CONSTRAINT

    PK_tblEmailType PRIMARY KEY CLUSTERED 

    (

    EmailTypeID

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

GO

COMMIT

BEGIN TRANSACTION

GO

ALTER TABLE dbo.tblUserEventEmail ADD CONSTRAINT

    FK_tblUserEventEmail_tblEmailType FOREIGN KEY

    (

    EmailTypeID

    ) REFERENCES dbo.tblEmailType

    (

    EmailTypeID

    ) ON UPDATE  NO ACTION 

     ON DELETE  NO ACTION 

GO

COMMIT