all the columns of a Foreign Key

Hi,
I have a table with a Foreign Key. I need to know which Fields of that table
are in that Foreign Key, to which other Table (that contains the Primary
Key) they are linked, and to which Fields in that primary Key they are
Linked...
I found a query on the internet that did this job almost fine, but it
doesn't work anymore when the Primary Key consist of more than one Field...
As you can see in the result I can't see if ClientID is linked to ClientID
(record 1) or to CodeArticle (record 2).
does anybody knows how I can achieve this? This info is in the SQL Server,
so there should be a way to get it back I guess'
Thanks a lot in advance,
Pieter
The records:
tblArticleClientSodimex | ClientID | tblArticleClient | ClientID |
FK_tblArticleClientSodimex_tblArticleClient
tblArticleClientSodimex | CodeArticle | tblArticleClient | ClientID |
FK_tblArticleClientSodimex_tblArticleClient
tblArticleClientSodimex | ClientID | tblArticleClient | CodeArticle |
FK_tblArticleClientSodimex_tblArticleClient
tblArticleClientSodimex | CodeArticle | tblArticleClient | CodeArticle |
FK_tblArticleClientSodimex_tblArticleClient
The query:
SELECT
FK_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN
(
SELECT
i1.TABLE_NAME, i2.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT
ON PT.TABLE_NAME = PK.TABLE_NAMEAh! I found it alreay myself!
I was able to put the ORDINAL_POSITION in it..
this is the changed query that seems to work fine...
SELECT
FK_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
ON (C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME)
INNER JOIN
(
SELECT
i1.TABLE_NAME, i2.COLUMN_NAME, i2.ORDINAL_POSITION
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT
ON (PT.TABLE_NAME = PK.TABLE_NAME) AND (CU.ORDINAL_POSITION =PT.ORDINAL_POSITION)
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:%23uZde2woFHA.708@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a table with a Foreign Key. I need to know which Fields of that
table
> are in that Foreign Key, to which other Table (that contains the Primary
> Key) they are linked, and to which Fields in that primary Key they are
> Linked...
> I found a query on the internet that did this job almost fine, but it
> doesn't work anymore when the Primary Key consist of more than one
Field...
> As you can see in the result I can't see if ClientID is linked to ClientID
> (record 1) or to CodeArticle (record 2).
> does anybody knows how I can achieve this? This info is in the SQL Server,
> so there should be a way to get it back I guess'
> Thanks a lot in advance,
> Pieter
> The records:
> tblArticleClientSodimex | ClientID | tblArticleClient | ClientID |
> FK_tblArticleClientSodimex_tblArticleClient
> tblArticleClientSodimex | CodeArticle | tblArticleClient | ClientID |
> FK_tblArticleClientSodimex_tblArticleClient
> tblArticleClientSodimex | ClientID | tblArticleClient | CodeArticle |
> FK_tblArticleClientSodimex_tblArticleClient
> tblArticleClientSodimex | CodeArticle | tblArticleClient | CodeArticle
|
> FK_tblArticleClientSodimex_tblArticleClient
> The query:
> SELECT
> FK_Table = FK.TABLE_NAME,
> FK_Column = CU.COLUMN_NAME,
> PK_Table = PK.TABLE_NAME,
> PK_Column = PT.COLUMN_NAME,
> Constraint_Name = C.CONSTRAINT_NAME
> FROM
> INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
> INNER JOIN
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
> ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
> INNER JOIN
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
> ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
> INNER JOIN
> INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
> ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
> INNER JOIN
> (
> SELECT
> i1.TABLE_NAME, i2.COLUMN_NAME
> FROM
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
> INNER JOIN
> INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
> ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
> WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
> ) PT
> ON PT.TABLE_NAME = PK.TABLE_NAME
>