SQL 删除原主键 增加新主键自增长列 重建主键

--定义变量存储主键
DECLARE @Constraint_Name    varchar(128) 
--获取主键
SELECT @Constraint_Name = Name  
    FROM dbo.sysobjects  
    WHERE Xtype = 'PK'  
          and Parent_Obj = 
        (
            SELECT [ID]
                FROM dbo.sysobjects
                WHERE id = OBJECT_ID(N'TestTableName')
                    and OBJECTPROPERTY(OBJECT_ID(N'TestTableName'), N'IsUserTable') = 1
        )


PRINT @Constraint_Name
--删除原主键
EXEC ('ALTER TABLE TestTableName DROP CONSTRAINT '+@Constraint_Name )  

--加入新自增长列并设置主键
IF EXISTS(SELECT * FROM syscolumns WHERE id=OBJECT_ID('TestTableName') and name='TestID')  
    BEGIN
        ALTER TABLE TestTableName DROP COLUMN TestID
        ALTER TABLE TestTableName ADD TestID BIGINT IDENTITY(1,1) 
        ALTER Table TestTableName ADD CONSTRAINT PK_TestTableName PRIMARY KEY (TestID) 
    END

ELSE 
 BEGIN
　　  ALTER TABLE TestTableName ADD TestID BIGINT IDENTITY(1,1) 
 　　 ALTER Table TestTableName ADD CONSTRAINT PK_TestTableName PRIMARY KEY (TestID) 
 END