如何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]GOCREATETABLE[dbo].[a]([id][int]NOTNULL,[title][char](10)COLLATEChinese_PRC_CI_ASNULL)ON[PRIMARY]GO--2.加identity属性BEGINTRANSACTIONSETQUOTED_IDENTIFIERONSETTRANSACTIONISOLATIONLEVELSERIALIZABLESETARITHABORTONSETNUMERIC_ROUNDABORTOFFSETCONCAT_NULL_YIELDS_NULLONSETANSI_NULLSONSETANSI_PADDINGONSETANSI_WARNINGSONCOMMITBEGINTRANSACTIONCREATETABLEdbo.Tmp_a(idintNOTNULLIDENTITY(1,1),titlechar(10)NULL)ON[PRIMARY]GOSETIDENTITY_INSERTdbo.Tmp_aONGOIFEXISTS(SELECT*FROMdbo.a)EXEC('INSERTINTOdbo.Tmp_a(id,title)SELECTid,titleFROMdbo.aTABLOCKX')GOSETIDENTITY_INSERTdbo.Tmp_aOFFGODROPTABLEdbo.aGOEXECUTEsp_renameN'dbo.Tmp_a',N'a','OBJECT'GOALTERTABLEdbo.aADDCONSTRAINTPK_aPRIMARYKEYCLUSTERED(id)ON[PRIMARY]GOCOMMIT--3.去掉identity属性BEGINTRANSACTIONSETQUOTED_IDENTIFIERONSETTRANSACTIONISOLATIONLEVELSERIALIZABLESETARITHABORTONSETNUMERIC_ROUNDABORTOFFSETCONCAT_NULL_YIELDS_NULLONSETANSI_NULLSONSETANSI_PADDINGONSETANSI_WARNINGSONCOMMITBEGINTRANSACTIONCREATETABLEdbo.Tmp_a(idintNOTNULL,titlechar(10)NULL)ON[PRIMARY]GOIFEXISTS(SELECT*FROMdbo.a)EXEC('INSERTINTOdbo.Tmp_a(id,title)SELECTid,titleFROMdbo.aTABLOCKX')GODROPTABLEdbo.aGOEXECUTEsp_renameN'dbo.Tmp_a',N'a','OBJECT'GOALTERTABLEdbo.aADDCONSTRAINTPK_aPRIMARYKEYCLUSTERED(id)ON[PRIMARY]GOCOMMIT
本文地址：http://www.45fan.com/a/question/71223.html