Solved

Adding Primary Keys Dynamically

Posted on 2013-11-15
1
180 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:
ScottPletcher 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

760 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now