[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Query to Create Primary Keys

Posted on 2013-11-16
3
Medium Priority
?
294 Views
Last Modified: 2013-11-21
I am tasked to create a script to archive tables in a database.  The script should be reusable with minor tweaking.

I have created a dynamic query to create and populate the tables.

Now I need to add Primary Keys to the archive tables to match the primary keys on the non-archive tables.

I have created a #table that has 2 columns  (tablename and primarykey)

I am reading the #table in a while loop and running the following statement

select @vssql = 'ALTER TABLE ' + @vstablename + '_ARCHIVE' + ' ADD CONSTRAINT ' +  'pk_' + @PKColName + ' PRIMARY KEY ' +'(' + @PKColName + ')'

Everything goes OK, until I get to one of the 5 tables that has a composite primary key.

There are over 200 tables with a single column primary key and only 5 with a composite primary key.  

Can someone suggest a query that would handle both the single and composite primary keys in one pass.  

Thanks in advance.
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
  • 2
3 Comments
 
LVL 23

Accepted Solution

by:
Rajkumar Gs earned 2000 total points
ID: 39654112
Add more columns in the temp table with column names like
tablename and primarykey1,primarykey2,...

Then check whether the parameters @PKColName2, @PKColName3,... has values. If there create dynamic queries accordingly

Raj
0
 

Author Comment

by:sherbug1015
ID: 39654549
Raj

Any chance you can help me with the query.  I have attached a subset of the dataset

This is the query that compiles the datatset

insert into #tmppkrows

SELECT KU.table_name as tablename,column_name as primarykeycolumn1,ku.ordinal_position
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU
ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME
ORDER BY KU.TABLE_NAME, KU.ORDINAL_POSITION;

 Instead of a flat dataset you are saying to create a pivoted table liked:

tablename   1  2   3

where 1,2,3 represent the ordinal position.

Can you help me with the pivoted table query.  I am not that familiar with them.

Thanks.
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 39665576
Hi

I appologize as I couldn't keep in touch with your question, as I am busy these days.

Hope everything went fine ?

Raj
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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

649 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