如何sql语句给字段添加和去掉identity属性?
如何sql语句给字段添加和去掉identity属性?
--在SQLSERVER的EnterpriseManage中,先建一个表--然后右击,选择DesignTable,添加了Identity属性后,用SaveChangeScript按钮
--看看系统是怎么做的,去掉Identity同理.
--假如建了表a,id有identity属性
------------------------------------------------
--1.创建表
ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[a]')andOBJECTPROPERTY(id,N'IsUserTable')=1)
droptable[dbo].[a]
GO
CREATETABLE[dbo].[a](
[id][int]NOTNULL,
[title][char](10)COLLATEChinese_PRC_CI_ASNULL
)ON[PRIMARY]
GO
--2.加identity属性
BEGINTRANSACTION
SETQUOTED_IDENTIFIERON
SETTRANSACTIONISOLATIONLEVELSERIALIZABLE
SETARITHABORTON
SETNUMERIC_ROUNDABORTOFF
SETCONCAT_NULL_YIELDS_NULLON
SETANSI_NULLSON
SETANSI_PADDINGON
SETANSI_WARNINGSON
COMMIT
BEGINTRANSACTION
CREATETABLEdbo.Tmp_a
(
idintNOTNULLIDENTITY(1,1),
titlechar(10)NULL
)ON[PRIMARY]
GO
SETIDENTITY_INSERTdbo.Tmp_aON
GO
IFEXISTS(SELECT*FROMdbo.a)
EXEC('INSERTINTOdbo.Tmp_a(id,title)
SELECTid,titleFROMdbo.aTABLOCKX')
GO
SETIDENTITY_INSERTdbo.Tmp_aOFF
GO
DROPTABLEdbo.a
GO
EXECUTEsp_renameN'dbo.Tmp_a',N'a','OBJECT'
GO
ALTERTABLEdbo.aADDCONSTRAINT
PK_aPRIMARYKEYCLUSTERED
(
id
)ON[PRIMARY]
GO
COMMIT
--3.去掉identity属性
BEGINTRANSACTION
SETQUOTED_IDENTIFIERON
SETTRANSACTIONISOLATIONLEVELSERIALIZABLE
SETARITHABORTON
SETNUMERIC_ROUNDABORTOFF
SETCONCAT_NULL_YIELDS_NULLON
SETANSI_NULLSON
SETANSI_PADDINGON
SETANSI_WARNINGSON
COMMIT
BEGINTRANSACTION
CREATETABLEdbo.Tmp_a
(
idintNOTNULL,
titlechar(10)NULL
)ON[PRIMARY]
GO
IFEXISTS(SELECT*FROMdbo.a)
EXEC('INSERTINTOdbo.Tmp_a(id,title)
SELECTid,titleFROMdbo.aTABLOCKX')
GO
DROPTABLEdbo.a
GO
EXECUTEsp_renameN'dbo.Tmp_a',N'a','OBJECT'
GO
ALTERTABLEdbo.aADDCONSTRAINT
PK_aPRIMARYKEYCLUSTERED
(
id
)ON[PRIMARY]
GO
COMMIT