Link to home
Start Free TrialLog in
Avatar of sherbug1015
sherbug1015Flag for United States of America

asked on

Query to Create Primary Keys

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.
ASKER CERTIFIED SOLUTION
Avatar of Rajkumar Gs
Rajkumar Gs
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sherbug1015

ASKER

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.
Hi

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

Hope everything went fine ?

Raj