约束查询删除与主键查询切换的SQL写法

--原表主键为主键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