Link to home
Start Free TrialLog in
Avatar of 25112
25112

asked on

pk-order

is there a way to check by code which columns and their order in the composite key?
Avatar of 25112
25112

ASKER

example: PK has 4 columns (col1,col2,col3,col4) in the composite key..

does a system view hold the order
col1 1
col2 2
col3 3
col4 4
use MyDatabase
go

select column_name, ordinal_position from information_schema.key_column_usage
where table_name = 'MyTableName'
ASKER CERTIFIED SOLUTION
Avatar of rbride
rbride

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
Note: the information_schema.key_column_usage view also has a column for constraint_name, which you may want to add to your query. This is just in case there is more than one constraint in a given table.
Buttercup1's method is simpler BUT you will need to still join sys.indexes.name against INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME because foreign key constraints will also be listed - and I don't think you want them :-).
rbride is correct, that does include foreign keys. You can use the table sys.key_constraints to filter them out, getting only primary keys in your result:

select constraint_name, column_name, ordinal_position
from information_schema.key_column_usage
where
table_name = 'MyTableName'
and constraint_name in
(select name from sys.key_constraints)
order by constraint_name, ordinal_position
Avatar of 25112

ASKER

thanks..

how could you avoid FK from INFORMATION_SCHEMA.KEY_COLUMN_USAGE in SQL 2000?
SOLUTION
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 25112

ASKER

sounds good..