Solved

pk-order

Posted on 2011-09-21
9
252 Views
Last Modified: 2012-05-12
is there a way to check by code which columns and their order in the composite key?
0
Comment
Question by:25112
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 5

Author Comment

by:25112
ID: 36575763
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
 
LVL 11

Expert Comment

by:Simone B
ID: 36575870
use MyDatabase
go

select column_name, ordinal_position from information_schema.key_column_usage
where table_name = 'MyTableName'
0
 
LVL 4

Accepted Solution

by:
rbride earned 250 total points
ID: 36575887
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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
LVL 11

Expert Comment

by:Simone B
ID: 36575907
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
 
LVL 4

Expert Comment

by:rbride
ID: 36575949
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
 
LVL 11

Expert Comment

by:Simone B
ID: 36576093
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
 
LVL 5

Author Comment

by:25112
ID: 36576101
thanks..

how could you avoid FK from INFORMATION_SCHEMA.KEY_COLUMN_USAGE in SQL 2000?
0
 
LVL 11

Assisted Solution

by:Simone B
Simone B earned 250 total points
ID: 36576186
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
 
LVL 5

Author Comment

by:25112
ID: 36576862
sounds good..
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql query help 15 55
T-SQL Query - Group By Year 3 32
Finding Where Clause Value in SQL Views and SP 21 42
Assigning Database Principals to Database Roles 3 19
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question