SQL Server 2008 Script to remove a PK constraint that has a system-generated name

Question

I am trying to add a clustered index to an existing table in SQL Server 2008 and should be an automated script because this table exists in multiple databases on multiple servers.

To add a clustered index, I need to remove the PK constraint in the table, and then re-add it as non-clustered. The problem is that the PK constraint name is automatically generated and a pointer is added to the end, so it resembles "PK_ [Table] _D9F9203400".

The name in all the databases is different, and I'm not sure how to write an automated script that reduces the PK limit for a table in which I don't know the name of the restriction. Any help is appreciated!

UPDATE:

The answer below is what I used. Full script:

Declare @Val varchar(100) Declare @Cmd varchar(1000) Set @Val = ( select name from sysobjects where xtype = 'PK' and parent_obj = (object_id('[Schema].[Table]')) ) Set @Cmd = 'ALTER TABLE [Table] DROP CONSTRAINT ' + @Val Exec (@Cmd) GO ALTER TABLE [Table] ADD CONSTRAINT PK_Table PRIMARY KEY NONCLUSTERED (TableId) GO CREATE UNIQUE CLUSTERED INDEX IX_Table_Column ON Table (Column) GO 

Answer 1

You can find the name of the constraint and write a bit of dynamic SQL to handle the drop.

 SELECT name FROM sys.key_constraints WHERE parent_object_id = object_id('YourSchemaName.YourTableName') AND type = 'PK';