Find the Forign keys's primary key table

I hava a table, called Items which has 2 foriegn keys. i want to find the
forign keys's primary key table through sql query.
Please anybody help me.
Regards,
R.SathiamoorthyIf you're using SQL Server 2005 you can piece together the info from
the sys.foreign_keys and sys.foreign_key_columns views.
object_id in sys.fk is the object_id of the constraint
parent_object_id in sys.fk is the table that the constraint is created
in
constraint_object_id in sys.fkc relates to object_id in sys.fk
parent_object_id in sys.fkc is the table that the constraint is
created in
parent_column_id is the column on which the constraint is created
referenced_object_id is the table the constraint is referencing
referenced_column_id is the column in the referenced table that is
used in the constraint.
The joining to sys.tables and sys.columns will get you the info you
require.
Cheers,
Jason Lepack
On Sep 7, 6:05 am, "Sathiamoorthy" <some...@.microsoft.com> wrote:
> I hava a table, called Items which has 2 foriegn keys. i want to find the
> forign keys's primary key table through sql query.
> Please anybody help me.
> Regards,
> R.Sathiamoorthy|||I though this was an intresting question, so I worte a little code. Sorry I
didn't have time to finish it, gotta go to work.
-- Create and use a NEW, empty database.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id =OBJECT_ID(N'[dbo].[tab2]') AND type in (N'U'))
DROP TABLE [dbo].[tab2]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id =OBJECT_ID(N'[dbo].[tab1]') AND type in (N'U'))
DROP TABLE [dbo].[tab1]
CREATE TABLE tab1 (col1 INT, CONSTRAINT PK_tab1 PRIMARY KEY(col1))
CREATE TABLE tab2 (
col1 INT,
col2 INT,
CONSTRAINT PK_tab2 PRIMARY KEY(col1, col2),
CONSTRAINT FK_tab2_tab1 FOREIGN KEY (col1) REFERENCES tab1(col1)
)
SELECT so1.[name], so1.id, so2.name, so2.id, so2.xtype, so2.type, *
FROM sysobjects so1
INNER JOIN sysobjects so2 ON so2.parent_obj = so1.id
WHERE so1.[name] IN ('tab1', 'tab2')
SELECT sc.*, 'XXX' xxx, so.* FROM sysconstraints sc INNER JOIN sysobjects so
ON so.id = sc.id
DECLARE @.HasFK SYSNAME
SELECT @.HasFK = name
from sysobjects
where id in (
SELECT fkeyid
FROM sysreferences sr
WHERE constid IN (
SELECT so2.id
FROM sysobjects so1
INNER JOIN sysobjects so2 ON so2.parent_obj = so1.id
)
)
DECLARE @.References SYSNAME
SELECT @.References = name
from sysobjects
where id in (
SELECT rkeyid
FROM sysreferences sr
WHERE constid IN (
SELECT so2.id
FROM sysobjects so1
INNER JOIN sysobjects so2 ON so2.parent_obj = so1.id
)
)
SELECT @.HasFK + ' has FK to ' + @.References
"Sathiamoorthy" <someone@.microsoft.com> wrote in message
news:uhuElgT8HHA.4476@.TK2MSFTNGP06.phx.gbl...
>I hava a table, called Items which has 2 foriegn keys. i want to find the
> forign keys's primary key table through sql query.
> Please anybody help me.
> Regards,
> R.Sathiamoorthy
>|||See my post 'sqlschema'
"Jay" <spam@.nospam.org> wrote in message
news:%23gwFK8V8HHA.1208@.TK2MSFTNGP03.phx.gbl...
>I though this was an intresting question, so I worte a little code. Sorry I
>didn't have time to finish it, gotta go to work.
> -- Create and use a NEW, empty database.
> IF EXISTS (SELECT * FROM sys.objects WHERE object_id => OBJECT_ID(N'[dbo].[tab2]') AND type in (N'U'))
> DROP TABLE [dbo].[tab2]
> IF EXISTS (SELECT * FROM sys.objects WHERE object_id => OBJECT_ID(N'[dbo].[tab1]') AND type in (N'U'))
> DROP TABLE [dbo].[tab1]
> CREATE TABLE tab1 (col1 INT, CONSTRAINT PK_tab1 PRIMARY KEY(col1))
> CREATE TABLE tab2 (
> col1 INT,
> col2 INT,
> CONSTRAINT PK_tab2 PRIMARY KEY(col1, col2),
> CONSTRAINT FK_tab2_tab1 FOREIGN KEY (col1) REFERENCES tab1(col1)
> )
> SELECT so1.[name], so1.id, so2.name, so2.id, so2.xtype, so2.type, *
> FROM sysobjects so1
> INNER JOIN sysobjects so2 ON so2.parent_obj = so1.id
> WHERE so1.[name] IN ('tab1', 'tab2')
> SELECT sc.*, 'XXX' xxx, so.* FROM sysconstraints sc INNER JOIN sysobjects
> so ON so.id = sc.id
> DECLARE @.HasFK SYSNAME
> SELECT @.HasFK = name
> from sysobjects
> where id in (
> SELECT fkeyid
> FROM sysreferences sr
> WHERE constid IN (
> SELECT so2.id
> FROM sysobjects so1
> INNER JOIN sysobjects so2 ON so2.parent_obj = so1.id
> )
> )
> DECLARE @.References SYSNAME
> SELECT @.References = name
> from sysobjects
> where id in (
> SELECT rkeyid
> FROM sysreferences sr
> WHERE constid IN (
> SELECT so2.id
> FROM sysobjects so1
> INNER JOIN sysobjects so2 ON so2.parent_obj = so1.id
> )
> )
> SELECT @.HasFK + ' has FK to ' + @.References
>
> "Sathiamoorthy" <someone@.microsoft.com> wrote in message
> news:uhuElgT8HHA.4476@.TK2MSFTNGP06.phx.gbl...
>>I hava a table, called Items which has 2 foriegn keys. i want to find the
>> forign keys's primary key table through sql query.
>> Please anybody help me.
>> Regards,
>> R.Sathiamoorthy
>>
>