--
在SQL SERVER的Enterprise Manage中,先建一个表
-- 然后右击,选择Design Table,添加了Identity属性后,用Save Change Script按钮
-- 看看系统是怎么做的,去掉Identity同理.
-- 假如建了表a,id有identity属性
-- ----------------------------------------------
-- 1.创建表
if exists ( select * from dbo.sysobjects where id = object_id (N ' [dbo].[a] ' ) and OBJECTPROPERTY (id, N ' IsUserTable ' ) = 1 )
drop table [ dbo ] . [ a ]
GO
CREATE TABLE [ dbo ] . [ a ] (
[ id ] [ int ] NOT NULL ,
[ title ] [ char ] ( 10 ) COLLATE Chinese_PRC_CI_AS NULL
) ON [ PRIMARY ]
GO
-- 2.加identity属性
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
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
CREATE TABLE dbo.Tmp_a
(
id int NOT NULL IDENTITY ( 1 , 1 ),
title char ( 10 ) NULL
) ON [ PRIMARY ]
GO
SET IDENTITY_INSERT dbo.Tmp_a ON
GO
IF EXISTS ( SELECT * FROM dbo.a)
EXEC ( ' INSERT INTO dbo.Tmp_a (id, title)
SELECT id, title FROM dbo.a TABLOCKX ' )
GO
SET IDENTITY_INSERT dbo.Tmp_a OFF
GO
DROP TABLE dbo.a
GO
EXECUTE sp_rename N ' dbo.Tmp_a ' , N ' a ' , ' OBJECT '
GO
ALTER TABLE dbo.a ADD CONSTRAINT
PK_a PRIMARY KEY CLUSTERED
(
id
) ON [ PRIMARY ]
GO
COMMIT
-- 3.去掉identity属性
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
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
CREATE TABLE dbo.Tmp_a
(
id int NOT NULL ,
title char ( 10 ) NULL
) ON [ PRIMARY ]
GO
IF EXISTS ( SELECT * FROM dbo.a)
EXEC ( ' INSERT INTO dbo.Tmp_a (id, title)
SELECT id, title FROM dbo.a TABLOCKX ' )
GO
DROP TABLE dbo.a
GO
EXECUTE sp_rename N ' dbo.Tmp_a ' , N ' a ' , ' OBJECT '
GO
ALTER TABLE dbo.a ADD CONSTRAINT
PK_a PRIMARY KEY CLUSTERED
(
id
) ON [ PRIMARY ]
GO
COMMIT
-- 然后右击,选择Design Table,添加了Identity属性后,用Save Change Script按钮
-- 看看系统是怎么做的,去掉Identity同理.
-- 假如建了表a,id有identity属性
-- ----------------------------------------------
-- 1.创建表
if exists ( select * from dbo.sysobjects where id = object_id (N ' [dbo].[a] ' ) and OBJECTPROPERTY (id, N ' IsUserTable ' ) = 1 )
drop table [ dbo ] . [ a ]
GO
CREATE TABLE [ dbo ] . [ a ] (
[ id ] [ int ] NOT NULL ,
[ title ] [ char ] ( 10 ) COLLATE Chinese_PRC_CI_AS NULL
) ON [ PRIMARY ]
GO
-- 2.加identity属性
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
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
CREATE TABLE dbo.Tmp_a
(
id int NOT NULL IDENTITY ( 1 , 1 ),
title char ( 10 ) NULL
) ON [ PRIMARY ]
GO
SET IDENTITY_INSERT dbo.Tmp_a ON
GO
IF EXISTS ( SELECT * FROM dbo.a)
EXEC ( ' INSERT INTO dbo.Tmp_a (id, title)
SELECT id, title FROM dbo.a TABLOCKX ' )
GO
SET IDENTITY_INSERT dbo.Tmp_a OFF
GO
DROP TABLE dbo.a
GO
EXECUTE sp_rename N ' dbo.Tmp_a ' , N ' a ' , ' OBJECT '
GO
ALTER TABLE dbo.a ADD CONSTRAINT
PK_a PRIMARY KEY CLUSTERED
(
id
) ON [ PRIMARY ]
GO
COMMIT
-- 3.去掉identity属性
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
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
CREATE TABLE dbo.Tmp_a
(
id int NOT NULL ,
title char ( 10 ) NULL
) ON [ PRIMARY ]
GO
IF EXISTS ( SELECT * FROM dbo.a)
EXEC ( ' INSERT INTO dbo.Tmp_a (id, title)
SELECT id, title FROM dbo.a TABLOCKX ' )
GO
DROP TABLE dbo.a
GO
EXECUTE sp_rename N ' dbo.Tmp_a ' , N ' a ' , ' OBJECT '
GO
ALTER TABLE dbo.a ADD CONSTRAINT
PK_a PRIMARY KEY CLUSTERED
(
id
) ON [ PRIMARY ]
GO
COMMIT