Drop and recreate primary key on a table
The following is an example of how to drop a primary key (if it exists) on a table and recreate it.
-- declare a variable to hold the name of the primary key DECLARE @sPrimaryKeyName varchar(300) -- get the name of the primary key, if any SELECT @sPrimaryKeyName = a.[name] from sys.indexes a where a.[object_id] = object_id('MyTable') and a.is_primary_key = 1 -- see if we got back a primary key name if (@sPrimaryKeyName is not null) begin -- we have a primary key on the table, drop it now. exec ('ALTER TABLE [MyTable] DROP CONSTRAINT ' + @sPrimaryKeyName) end GO --use this line if the primary key is to have a specific name: --ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED -- add the primary key back on the table ALTER TABLE [dbo].[MyTable] ADD PRIMARY KEY CLUSTERED ( [Col1] ASC, [Col2] ASC, [Col3] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO