pk-order

is there a way to check by code which columns and their order in the composite key?
LVL 5
25112Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
rbrideConnect With a Mentor Commented:
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
 
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
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
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 BConnect With a Mentor Senior 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
All Courses

From novice to tech pro — start learning today.