修改主键示例

因为前期表设计不合理，为提高性能，需要将主键重新设置为其他字段。以下是示例脚本。

--修改主键示例

DECLARE @keyColName VARCHAR(50)
SELECT @keyColName = name FROM SysColumns WHERE id=Object_Id(‘表名‘) and colid=(select top 1 colid from sysindexkeys where id=Object_Id(‘表名‘))
SELECT @keyColName

IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N‘[dbo].[表名]‘) AND name = N‘PK_表名‘) AND @keyColName = ‘原主键字段‘
BEGIN

ALTER TABLE [dbo].[表名] DROP CONSTRAINT [PK_表名]
ALTER TABLE [dbo].[表名] ADD CONSTRAINT [PK_表名] PRIMARY KEY CLUSTERED ( 新主键字段 ASC)
PRINT ‘表名修改主键为新主键字段‘
End

SELECT @keyColName = name FROM SysColumns WHERE id=Object_Id(‘表名‘) and colid=(select top 1 colid from sysindexkeys where id=Object_Id(‘表名‘))
SELECT @keyColName

修改主键示例

原文：https://www.cnblogs.com/059212315/p/14036860.html