Adding Primary Keys Dynamically
Posted on 2013-11-15
I am trying to add a primary key to 160 tables with dynamic sql
select @vssql = 'ALTER TABLE ' + @vstablename + '_Archive' + ' ADD CONSTRAINT ' + 'pk_' + @PKColName + ' PRIMARY KEY ' +'(' + @PKColName + ')'
Many of the tables have a primary key column with the same name. for example recordid is very popular. I am hitting errors when running my script and sql complains that there is already a object name pk_recordid in the database.
I am assuming that it doesn't want to create the same primary key name for multiple tables. Is there anyway around this?
Thanks for your help.