sherbug1015
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi
I appologize as I couldn't keep in touch with your question, as I am busy these days.
Hope everything went fine ?
Raj
I appologize as I couldn't keep in touch with your question, as I am busy these days.
Hope everything went fine ?
Raj
ASKER
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.ordin
FROM INFORMATION_SCHEMA.TABLE_C
INNER JOIN
INFORMATION_SCHEMA.KEY_COL
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.