単一テーブルを拡張するSQLメモ

■単一テーブルを拡張するSQLメモ(DROP&CREATEバージョン)
BEGIN TRANSACTION
GO

SELECT * INTO [Temp_TableA] FROM [SchemaZ].[TableA]
GO

DROP TABLE [SchemaZ].[TableA]
GO

CREATE TABLE [SchemaZ].[TableA]
(
[item1] [int] IDENTITY(1,1) NOT NULL,
[item2] [timestamp] NOT NULL,
[item3] [varchar] (240) NOT NULL CONSTRAINT [DF_TableA_item3] DEFAULT (''),
[item4] [datetime] NOT NULL CONSTRAINT [DF_TableA_item4] DEFAULT (0),
[item5] [nvarchar] (32) NOT NULL CONSTRAINT [DF_TableA_item5] DEFAULT (''),
[item6] [binary] (64) NULL,
) ON [SchemaZ]
GO

EXEC sys.sp_addextendedproperty
@name=N'LogicalName', @value=N'テーブル名',
@level0type=N'SCHEMA',@level0name=N'SchemaZ',
@level1type=N'TABLE',@level1name=N'TableA'
GO

ALTER TABLE [SchemaZ].[TableA] ADD
CONSTRAINT [PK_TableA_ID] PRIMARY KEY NONCLUSTERED
(
[ID] ASC
) ON [SchemaZ]
GO

CREATE UNIQUE CLUSTERED INDEX [UC_TableA_item3_item5] ON
[SchemaZ].[TableA]
(
[item3] ASC,
[item5] ASC
) ON [SchemaZ]
GO

SET IDENTITY_INSERT [SchemaZ].[TableA] ON
GO

IF EXISTS(SELECT * FROM [Temp_TableA])
EXEC('
INSERT INTO [SchemaZ].[TableA] (
[item1],[item2],[item3],[item4],[item5],[item6]
)
SELECT
[item1],[item2],[item3],[item4],[item5],[item6]
FROM [Temp_TableA] WITH (HOLDLOCK TABLOCKX)
')
GO

SET IDENTITY_INSERT [SchemaZ].[TableA] OFF
GO

DROP TABLE [Temp_TableA]
GO

COMMIT