Solved

pk-order

Posted on 2011-09-21
9
250 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
  • 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
 
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
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

911 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now