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

Question

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 1

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 ...