Solved

Adding Primary Keys Dynamically

Posted on 2013-11-15
1
183 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Suggested Solutions

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

738 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