Finding a Primary Key Constraint on the fly in SQL Server 2005

I have the following SQL:

 ALTER TABLE dbo.PS_userVariables DROP CONSTRAINT PK_PS_userVariables;
 ALTER TABLE dbo.PS_userVariables ADD PRIMARY KEY (varnumber, subjectID, userID, datasetID, listid, userVarTitle);

Since I have multiple environments, that PK_PS_userVariables constraint name is different on my different databases. How do I write a script that gets that name then adds it into my script?

Answer

While the typical best practice is to always explicitly name your constraints, you can get them dynamically from the catalog views:

DECLARE @table NVARCHAR(512), @sql NVARCHAR(MAX);

SELECT @table = N'dbo.PS_userVariables';

SELECT @sql = 'ALTER TABLE ' + @table 
    + ' DROP CONSTRAINT ' + name + ';'
    FROM sys.key_constraints
    WHERE [type] = 'PK'
    AND [parent_object_id] = OBJECT_ID(@table);

EXEC sp_executeSQL @sql;

ALTER TABLE dbo.PS_userVariables ADD CONSTRAINT ...