Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Adding Primary Keys Dynamically

Posted on 2013-11-15
1
Medium Priority
?
195 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 70

Accepted Solution

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

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

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
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…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Integration Management Part 2

824 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