Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 259
  • 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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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