Script : Rename Unnamed Primary Keys (sql 2000 compatible)

Everyone has done it, created a constraint without naming it.
On the surface it doesn't matter. It's the name of an internal object.
The problem surfaces when you use schema comparison tools and find your live and uat environments are incorrectly reported as being different.

To demonstrate -

ALTER TABLE [dbo].[mytable] ADD PRIMARY KEY CLUSTERED 
(
 [id] ASC
)

SQL randomly names the Primary Key PK__mytable__3213E83F0BC6C43E
Deleting the key and adding it again yields a name of PK__mytable__3213E83F0EA330E9

To prevent this behaviour, explicitly name the constraint like this -

ALTER TABLE [dbo].[Table_2] ADD CONSTRAINT [PKCI_id] PRIMARY KEY CLUSTERED 
(
 [id] ASC
)

If you've already had these keys get into production, you can locate and rename them programatically like this -

set @tablename = 'mytable'
set @newconstraintname = 'PKCI_myid'
 
-- find constraint name
select @constraintname = O.name
from sysobjects AS O
left join sysobjects AS T
    on O.parent_obj = T.id
where T.name = @tablename
  and O.xtype = 'PK'
 
SELECT @constraintname
 
-- rename if found
if not @constraintname is null
begin
    set @sql = 'sp_rename ''' + @constraintname + ''' , ''' + @newconstraintname + ''' , ''OBJECT'' ;'
    select @sql
    execute sp_executesql @sql
end

NB : This post is deliberately targeted at SQL 2000 (hence the use of the sysobjects table)