25112
asked on
pk-order
is there a way to check by code which columns and their order in the composite key?
use MyDatabase
go
select column_name, ordinal_position from information_schema.key_col umn_usage
where table_name = 'MyTableName'
go
select column_name, ordinal_position from information_schema.key_col
where table_name = 'MyTableName'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Note: the information_schema.key_col umn_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_COL UMN_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_col umn_usage
where
table_name = 'MyTableName'
and constraint_name in
(select name from sys.key_constraints)
order by constraint_name, ordinal_position
select constraint_name, column_name, ordinal_position
from information_schema.key_col
where
table_name = 'MyTableName'
and constraint_name in
(select name from sys.key_constraints)
order by constraint_name, ordinal_position
ASKER
thanks..
how could you avoid FK from INFORMATION_SCHEMA.KEY_COL UMN_USAGE in SQL 2000?
how could you avoid FK from INFORMATION_SCHEMA.KEY_COL
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
sounds good..
ASKER
does a system view hold the order
col1 1
col2 2
col3 3
col4 4