|
如何用sql语句为字段添加和去掉identity属性,在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
| |
