SQL SERVER 修改存储过程

USE [20231028140611]
GO
/****** Object:  StoredProcedure [dbo].[p_AlterPK]    Script Date: 2023/12/15 10:55:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[p_AlterPK](@PkName VARCHAR(30),
@TableName VARCHAR(30),
@FieldName VARCHAR(2000),
@IsClustered INT = 0)    --0非聚集   1聚集
AS
SET NOCOUNT ON
DECLARE @oldPkName VARCHAR(30)
DECLARE @strCluster VARCHAR(20)
DECLARE @clusterIndexName VARCHAR(100)
SELECT @oldPkName = name FROM sys.objects WHERE parent_object_id = object_id(@TableName) AND type='PK'
IF(LEN(@oldPkName)>0)
BEGIN
EXEC('ALTER TABLE ' + @TableName + ' DROP CONSTRAINT '+ @oldPkName)
END
SELECT @clusterIndexName = @TableName + '.' + name from sys.indexes where object_id = object_id(@TableName) and index_id = 1
SET @strCluster = ' NONCLUSTERED '
IF @IsClustered=1    --删除聚集索引
BEGIN
SET @strCluster = ' CLUSTERED '
IF(len(@clusterIndexName) > 0)
BEGIN
EXEC(' DROP INDEX '  + @clusterIndexName)    --删除聚集索引
END
END
IF(LEN(@FieldName)>0)
BEGIN
EXEC('ALTER TABLE ' + @TableName + ' ADD CONSTRAINT ' + @PkName + ' PRIMARY KEY ' + @strCluster + '(' + @FieldName + ')')
END