Solved

Query to Create Primary Keys

Posted on 2013-11-16
3
289 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
  • 2
3 Comments
 
LVL 23

Accepted Solution

by:
Rajkumar Gs earned 500 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

776 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