Drop and recreate primary key on an SQL Server temporary table

Question

I need to drop and rebuild a primary key in a temp table in SQL Server. The most common solution requires you to enter the name of the PK index. However, since this is inside a store procedure, doing so limits any parallel use of this sp.

CREATE TABLE #mytable (Date_ DATETIME NOT NULL, Name_ CHAR(50) NOT NULL, VALUE_ INT )
ALTER TABLE #mytable ADD PRIMARY KEY CLUSTERED (Date_, Name_)

Now I need to drop this primary key and create a new one on just the Name_ column.

For e.g.,

ALTER TABLE #mytable DROP OLD PK
ALTER TABLE #mytable ADD PRIMARY KEY CLUSTERED (Name_)

Answer 1

Dynamic SQL and NEWID() will give you a unique identifier that you can append to the PK's name.

DECLARE @GUID VARCHAR(50)

SELECT @GUID = REPLACE(NEWID(), '-', '')

PRINT @GUID


CREATE TABLE #mytable (Date_ DATETIME NOT NULL, Name_ CHAR(50) NOT NULL, VALUE_ INT )
EXEC('ALTER TABLE #mytable ADD CONSTRAINT PK_'+@GUID+' PRIMARY KEY CLUSTERED (Date_, Name_)')


EXEC('ALTER TABLE #mytable DROP CONSTRAINT PK_'+@GUID)
ALTER TABLE #mytable ADD PRIMARY KEY CLUSTERED (Name_)

DROP TABLE #MyTable