• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 263
  • Last Modified:

pk-order

is there a way to check by code which columns and their order in the composite key?
0
25112
Asked:
25112
  • 4
  • 3
  • 2
2 Solutions
 
25112Author Commented:
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
0
 
Simone BSenior E-Commerce AnalystCommented:
use MyDatabase
go

select column_name, ordinal_position from information_schema.key_column_usage
where table_name = 'MyTableName'
0
 
rbrideCommented:
Yes, there are several ways, we do it by looking at the meta data tables.

The ordering is in sys.index_columns.index_column_id.

You will also need
sys.index_columns, sys.indexes, sys.columns.

I haven't got time to write out the full select for for you now, it's simple enough.

You need for sys.indexes
in the where clause: sys.indexes.is_primary_key = 1

Join sys.indexes to sys.index_columns
  using object_id and index_id

Join sys.columns to sys.index_columns
  using object_id and column_id


Here's the MS documentation to the tables.
http://msdn.microsoft.com/en-US/library/ms173760(v=SQL.90).aspx
http://msdn.microsoft.com/en-US/library/ms176106(v=SQL.90).aspx
http://msdn.microsoft.com/en-US/library/ms175105(v=SQL.90).aspx
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Simone BSenior E-Commerce AnalystCommented:
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.
0
 
rbrideCommented:
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 :-).
0
 
Simone BSenior E-Commerce AnalystCommented:
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
0
 
25112Author Commented:
thanks..

how could you avoid FK from INFORMATION_SCHEMA.KEY_COLUMN_USAGE in SQL 2000?
0
 
Simone BSenior E-Commerce AnalystCommented:
Try this. I don't have SQL 2000 myself, so it's a bit of guesswork now.

select constraint_name, column_name, ordinal_position
from information_schema.key_column_usage
where
table_name = 'MyTableName'
and constraint_name in
(select name from sysobjects
where xtype = 'PK')
order by constraint_name, ordinal_position
0
 
25112Author Commented:
sounds good..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now