when you change the schema of a database, Management Studio makes it easy to create your change script.
first make your change.
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
