--原表主键为主键1和主键2,修改为主键1和主键3
--查出表原主键名
DECLARE @ConstraintName VARCHAR(100)
SELECT @ConstraintName = name
FROM dbo.sysobjects
WHERE xtype = 'PK'
AND parent_obj = ( SELECT [id]
FROM dbo.sysobjects
WHERE id = OBJECT_ID(N'[表名]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1
)
--删除表原主键约束
EXEC ('ALTER TABLE 表名 DROP CONSTRAINT '+@ConstraintName )
--添加新列,注意不为空须添加默认值约束
ALTER TABLE dbo.表名 ADD 主键3 VARCHAR(36) DEFAULT(NEWID()) NOT NULL
--为表设置新主键
ALTER TABLE dbo.表名 ADD CONSTRAINT 主键约束名 PRIMARY KEY (主键1,主键3)
GO
--查询新列的默认值约束名
DECLARE @name VARCHAR(100)
SELECT @name = b.name
FROM syscolumns a ,
sysobjects b
WHERE a.id = OBJECT_ID('表名')
AND b.id = a.cdefault
AND a.name = '主键3'
AND b.name LIKE 'DF%'
--删除新列的默认值约束
EXEC('ALTER TABLE 表名 DROP CONSTRAINT '+@name)
--如果表建立了索引,须先删除
IF EXISTS ( SELECT *
FROM sys.indexes
WHERE object_id = OBJECT_ID('表名')
AND name = '索引名' )
DROP INDEX 表名.索引名
GO
CREATE UNIQUE INDEX 索引名 ON 表名(列名)
GO
IF EXISTS ( SELECT *
FROM sys.indexes
WHERE object_id = OBJECT_ID('表名')
AND name = '索引名' )
PRINT '创建成功'
ELSE
PRINT '创建失败'
GO