Solved

Adding Primary Keys Dynamically

Posted on 2013-11-15
1
182 Views
Last Modified: 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 + ')'

exec(@vssql)

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.
0
Comment
Question by:sherbug1015
1 Comment
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 39652260
>> Is there anyway around this? <<

Prefix the constraint name with the table name too, before the 'pk_...'.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question