information_schema.key_column_usage system table

[parkc]

How do you change the order of key columns in a primary key and view the difference between two tables that have the same name?

For example, table x has a primary key called pk_x. PK_x contains two key columns x and y in the order 1 and 2 now I want to change it so that columns x and y are 2 and 1. How do I do that?

[ck9663]

i have not fully research about this, but i don't think it matters what the sequence of primary keys is. i think sql server's query optimization can intelligently handle your query as long as you have index on the most frequently searched keys/columns. it's also recommended that if the table will only have one index, make it clustered.

-- CK

[parkc]

Thank you for the response however, I am wanting to see if the order in which primary key columns have changed from one database to another. Therefore, it is important to know how to change the ordinal position for testing purposes.