How do I get all FK:PK mappings with the schema info?

David Thielen · Jun 26, 2009

Hi;

I need to get all FK

K mappings from a table where a database may
have two tables with the same name but different schemas. How can I do
this? (And is there a simplier select to do this?)

In addition, I need the schema.table, not just the table, of the table
the PK is in.

Note see below for why GetSchema will not work.

To get all columns descriptions I use:

Version 8:
cmd.CommandText = "SELECT fc.name AS foreign_column_1,
fc2.name AS foreign_column_2, p.name AS primary_table, " +
"rc.name AS primary_column_1, rc2.name AS
primary_column_2 FROM sysobjects AS f INNER JOIN " +
"sysobjects AS c ON f.parent_obj = c.id INNER JOIN
sysreferences AS r ON f.id = r.constid INNER JOIN " +
"sysobjects AS p ON r.rkeyid = p.id INNER JOIN
syscolumns AS rc ON r.rkeyid = rc.id AND " +
"r.rkey1 = rc.colid INNER JOIN syscolumns AS fc ON
r.fkeyid = fc.id AND r.fkey1 = fc.colid LEFT OUTER JOIN " +
"syscolumns AS rc2 ON r.rkeyid = rc2.id AND
r.rkey2 = rc.colid LEFT OUTER JOIN syscolumns AS fc2 ON " +
"r.fkeyid = fc2.id AND r.fkey2 = fc.colid WHERE
(f.type = 'F') AND (c.name = '" + sysTable + "')";

Version 9:
cmd.CommandText = "SELECT fc.name AS foreign_column_1,
fc2.name AS foreign_column_2, p.name AS primary_table, " +
"rc.name AS primary_column_1, rc2.name AS
primary_column_2 FROM sys.sysobjects AS f INNER JOIN " +
"sys.sysobjects AS c ON f.parent_obj = c.id INNER
JOIN sys.sysreferences AS r ON f.id = r.constid INNER JOIN " +
"sys.sysobjects AS p ON r.rkeyid = p.id INNER JOIN
sys.syscolumns AS rc ON r.rkeyid = rc.id AND " +
"r.rkey1 = rc.colid INNER JOIN sys.syscolumns AS
fc ON r.fkeyid = fc.id AND r.fkey1 = fc.colid LEFT OUTER JOIN " +
"sys.syscolumns AS rc2 ON r.rkeyid = rc2.id AND
r.rkey2 = rc.colid LEFT OUTER JOIN sys.syscolumns AS fc2 ON " +
"r.fkeyid = fc2.id AND r.fkey2 = fc.colid WHERE
(f.type = 'F') AND (c.name = '" + sysTable + "')";

I cannot use GetSchema() because GetSchema("ForeignKeys") tells me
that FK_Orders_Customers is set in dbo.Orders but does no give the FK
column and does not give the schema, table or column of the PK it maps
to.

??? - thanks - dave

david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm

David Thielen · Jun 26, 2009

The really critical item for me is I need the schema.table, not just
the table, of the table the PK is in.

thanks - dave

Hi;

I need to get all FKK mappings from a table where a database may
have two tables with the same name but different schemas. How can I do
this? (And is there a simplier select to do this?)

In addition, I need the schema.table, not just the table, of the table
the PK is in.

Note see below for why GetSchema will not work.

To get all columns descriptions I use:

Version 8:
cmd.CommandText = "SELECT fc.name AS foreign_column_1,
fc2.name AS foreign_column_2, p.name AS primary_table, " +
"rc.name AS primary_column_1, rc2.name AS
primary_column_2 FROM sysobjects AS f INNER JOIN " +
"sysobjects AS c ON f.parent_obj = c.id INNER JOIN
sysreferences AS r ON f.id = r.constid INNER JOIN " +
"sysobjects AS p ON r.rkeyid = p.id INNER JOIN
syscolumns AS rc ON r.rkeyid = rc.id AND " +
"r.rkey1 = rc.colid INNER JOIN syscolumns AS fc ON
r.fkeyid = fc.id AND r.fkey1 = fc.colid LEFT OUTER JOIN " +
"syscolumns AS rc2 ON r.rkeyid = rc2.id AND
r.rkey2 = rc.colid LEFT OUTER JOIN syscolumns AS fc2 ON " +
"r.fkeyid = fc2.id AND r.fkey2 = fc.colid WHERE
(f.type = 'F') AND (c.name = '" + sysTable + "')";

Version 9:
cmd.CommandText = "SELECT fc.name AS foreign_column_1,
fc2.name AS foreign_column_2, p.name AS primary_table, " +
"rc.name AS primary_column_1, rc2.name AS
primary_column_2 FROM sys.sysobjects AS f INNER JOIN " +
"sys.sysobjects AS c ON f.parent_obj = c.id INNER
JOIN sys.sysreferences AS r ON f.id = r.constid INNER JOIN " +
"sys.sysobjects AS p ON r.rkeyid = p.id INNER JOIN
sys.syscolumns AS rc ON r.rkeyid = rc.id AND " +
"r.rkey1 = rc.colid INNER JOIN sys.syscolumns AS
fc ON r.fkeyid = fc.id AND r.fkey1 = fc.colid LEFT OUTER JOIN " +
"sys.syscolumns AS rc2 ON r.rkeyid = rc2.id AND
r.rkey2 = rc.colid LEFT OUTER JOIN sys.syscolumns AS fc2 ON " +
"r.fkeyid = fc2.id AND r.fkey2 = fc.colid WHERE
(f.type = 'F') AND (c.name = '" + sysTable + "')";

I cannot use GetSchema() because GetSchema("ForeignKeys") tells me
that FK_Orders_Customers is set in dbo.Orders but does no give the FK
column and does not give the schema, table or column of the PK it maps
to.

??? - thanks - dave

david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm

david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm

David Thielen · Jun 27, 2009

Yep - good to go now.

BTW - is there a cleaner select to get this info? When it's this
complex I worry both that it is inefficient and that it is dependent
on something that may not work in future versions.

thanks - dave

As previously explained...

david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm

Lingzhi Sun [MSFT] · Jun 29, 2009

Hi David,

Data tables with same name but different schemas are supported in SQL
Server. For detail, please see my post in another your thread: [Is it
impossible to have schema1.dave and schema2.dave as tables in the same
DB?].

To retrieve all the FK, PK and other constraints information related to one
data table, we can use this T-SQL command:
===============================================================
exec sp_helpconstraint 'dbo.Products'
===============================================================

The schema name and the table name can be set in this query. By default,
the schema name will be ¡°dbo¡±. The query result contains three result
sets. The first one is the table object name. The second one consists of
the detailed information about the FK, PK and other constraints
information, including constraint type, constraint name, delete update
actions, status information and constraint key columns. The third set
contains all the FK related tables for the current table. DataReader can
be used to retrieve the data records in each result set.

http://msdn.microsoft.com/en-us/library/yf1a7f4f.aspx
http://msdn.microsoft.com/en-us/library/haa3afyz.aspx

SQL Server Management Objects (SMO) is another choice. But the expensive
performance cost is its largest drawback. For detail, please see my post
in another your case: [How do I get all column descriptions from a
schema.table?]

If you have any questions regarding this case, please feel free to let me
know.

Have a nice day!

Best Regards,
Lingzhi Sun
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

MSDN Managed Newsgroup support offering is for non-urgent issues where an
initial response from the community or a Microsoft Support Engineer within
2 business day is acceptable. Please note that each follow up response may
take approximately 2 business days as the support professional working with
you may need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations that require urgent,
real-time or phone-based interactions. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

David Thielen · Jul 5, 2009

Hi David,

Data tables with same name but different schemas are supported in SQL
Server. For detail, please see my post in another your thread: [Is it
impossible to have schema1.dave and schema2.dave as tables in the same
DB?].

To retrieve all the FK, PK and other constraints information related to one
data table, we can use this T-SQL command:
===============================================================
exec sp_helpconstraint 'dbo.Products'
===============================================================

The schema name and the table name can be set in this query. By default,
the schema name will be ¡°dbo¡±. The query result contains three result
sets. The first one is the table object name. The second one consists of
the detailed information about the FK, PK and other constraints
information, including constraint type, constraint name, delete update
actions, status information and constraint key columns. The third set
contains all the FK related tables for the current table. DataReader can
be used to retrieve the data records in each result set.
...

Hi;

I've found MARS to be problematic between it neededing to be
configured just right, etc. I have selects that work except that they
do not return the schema of the PK in the FK

K relationship. I've
tried to figure out what else I need to do an inner join on but have
had no luck.

What I use for ver 9 (Sql Server 2005) & later is:
cmd.CommandText = "SELECT fc.name AS foreign_column_1,
fc2.name AS foreign_column_2, p.name AS primary_table, " +
"rc.name AS primary_column_1, rc2.name AS
primary_column_2 FROM sys.sysobjects AS f INNER JOIN " +
"sys.sysobjects AS c ON f.parent_obj = c.id INNER
JOIN sys.sysreferences AS r ON f.id = r.constid INNER JOIN " +
"sys.sysobjects AS p ON r.rkeyid = p.id INNER JOIN
sys.syscolumns AS rc ON r.rkeyid = rc.id AND " +
"r.rkey1 = rc.colid INNER JOIN sys.syscolumns AS
fc ON r.fkeyid = fc.id AND r.fkey1 = fc.colid LEFT OUTER JOIN " +
"sys.syscolumns AS rc2 ON r.rkeyid = rc2.id AND
r.rkey2 = rc.colid LEFT OUTER JOIN sys.syscolumns AS fc2 ON " +
"r.fkeyid = fc2.id AND r.fkey2 = fc.colid WHERE
(f.type = 'F') AND (c.name = '" + sysTable + "')";

And for ver 8:
cmd.CommandText = "SELECT fc.name AS foreign_column_1,
fc2.name AS foreign_column_2, p.name AS primary_table, " +
"rc.name AS primary_column_1, rc2.name AS
primary_column_2 FROM sysobjects AS f INNER JOIN " +
"sysobjects AS c ON f.parent_obj = c.id INNER JOIN
sysreferences AS r ON f.id = r.constid INNER JOIN " +
"sysobjects AS p ON r.rkeyid = p.id INNER JOIN
syscolumns AS rc ON r.rkeyid = rc.id AND " +
"r.rkey1 = rc.colid INNER JOIN syscolumns AS fc ON
r.fkeyid = fc.id AND r.fkey1 = fc.colid LEFT OUTER JOIN " +
"syscolumns AS rc2 ON r.rkeyid = rc2.id AND
r.rkey2 = rc.colid LEFT OUTER JOIN syscolumns AS fc2 ON " +
"r.fkeyid = fc2.id AND r.fkey2 = fc.colid WHERE
(f.type = 'F') AND (c.name = '" + sysTable + "')";

Is there a way to get the schema from these and/or is there a better
select I can do? My code will work fine if I do a single select for
all FK

K mappings for a table or if I have to do it column by column.

What I need is for a given column (in a given table) that is a foreign
key, I can get the the schema, table, & column of the primary key it
maps to.

At present we are only handling the case of a single PK:FK mapping but
if I can get all back, better to have that info now.

How can I do this?

thanks - dave

david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm

Lingzhi Sun [MSFT] · Jul 6, 2009

Hi David,

Could you please make your concern clearer? Since the single FK/PK query
is working fine, do you mean you want to query the corresponding PK
information of all the FK columns in one table (column by column)? Or
given one FK column, we get the PK column and table, then from this PK
table, we retrieve all the other FK columns inside the PK table and again
retrieve the corresponding PK columns in other tables recursively?

Have a nice day, David!

Best Regards,
Lingzhi Sun
Microsoft Online Community Support

=================================================
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

This posting is provided "AS IS" with no warranties, and confers no rights.
=================================================

David Thielen · Jul 6, 2009

Hi David,

Could you please make your concern clearer? Since the single FK/PK query
is working fine, do you mean you want to query the corresponding PK
information of all the FK columns in one table (column by column)? Or
given one FK column, we get the PK column and table, then from this PK
table, we retrieve all the other FK columns inside the PK table and again
retrieve the corresponding PK columns in other tables recursively?
...

Hi;

It all works fine except it is not returning the schema of the PK. So
I get back for a table each FK and for each FK the table.column of the
PK it maps to. What I need is SCHEMA.table.column it maps to.

thanks - dave

david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm

Lingzhi Sun [MSFT] · Jul 8, 2009

Hi David,

Since the tables sys.foreign_key_columns (SQL Server 2005 & 2008) and
sysforeignkeys (SQL Server 2000) store the detailed information about the
FKs, we can use these two tables to query the corresponding FK's PK column,
table, and schema data.

For SQL Server 2000:
============================================================
SELECT scf.name AS parent_column_name,scr.name AS referenced_column_name,
OBJECT_NAME(sfk.rkeyid) AS referenced_table_name, USER_NAME(sos.uid) AS
referenced_table_schema FROM sysforeignkeys sfk
INNER JOIN syscolumns scf ON sfk.fkeyid = scf.id AND sfk.fkey = scf.colid
INNER JOIN syscolumns scr ON sfk.rkeyid = scr.id AND sfk.rkey = scr.colid
INNER JOIN sysobjects sos ON sfk.rkeyid = sos.id
WHERE OBJECT_NAME(sfk.fkeyid) = 'TableName'
============================================================

For SQL Server 2005 & 2008
============================================================
SELECT scp.name AS parent_column_name,scr.name AS
referenced_column_name,OBJECT_NAME(fkc.referenced_object_id) AS
referenced_table_name,OBJECT_SCHEMA_NAME(fkc.referenced_object_id) AS
referenced_table_schema FROM sys.foreign_key_columns AS fkc
INNER JOIN sys.columns scp
ON fkc.parent_object_id = scp.object_id
AND fkc.parent_column_id =scp.column_id
INNER JOIN sys.columns scr
ON fkc.referenced_object_id = scr.object_id
AND fkc.referenced_column_id =scr.column_id
WHERE OBJECT_NAME(fkc.parent_object_id)= 'TableName'
============================================================

I have tested the queries in Adventureworks database. David, please have
a test and tell me the testing result.

For some additional information, please see:
http://msdn.microsoft.com/en-us/library/ms186306.aspx
http://msdn.microsoft.com/en-us/library/aa260409(SQL.80).aspx
http://msdn.microsoft.com/en-us/library/ms187997.aspx
http://msdn.microsoft.com/en-us/library/ms187113.aspx

If you need any further assistance, please feel free to let me know.

Have a nice day!

Best Regards,
Lingzhi Sun
Microsoft Online Community Support

=================================================
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

This posting is provided "AS IS" with no warranties, and confers no rights.
=================================================

David Thielen · Jul 8, 2009

Hi David,

Since the tables sys.foreign_key_columns (SQL Server 2005 & 2008) and
sysforeignkeys (SQL Server 2000) store the detailed information about the
FKs, we can use these two tables to query the corresponding FK's PK column,
table, and schema data.

For SQL Server 2000:
============================================================
SELECT scf.name AS parent_column_name,scr.name AS referenced_column_name,
OBJECT_NAME(sfk.rkeyid) AS referenced_table_name, USER_NAME(sos.uid) AS
referenced_table_schema FROM sysforeignkeys sfk
INNER JOIN syscolumns scf ON sfk.fkeyid = scf.id AND sfk.fkey = scf.colid
INNER JOIN syscolumns scr ON sfk.rkeyid = scr.id AND sfk.rkey = scr.colid
INNER JOIN sysobjects sos ON sfk.rkeyid = sos.id
WHERE OBJECT_NAME(sfk.fkeyid) = 'TableName'
============================================================

For SQL Server 2005 & 2008
============================================================
SELECT scp.name AS parent_column_name,scr.name AS
referenced_column_name,OBJECT_NAME(fkc.referenced_object_id) AS
referenced_table_name,OBJECT_SCHEMA_NAME(fkc.referenced_object_id) AS
referenced_table_schema FROM sys.foreign_key_columns AS fkc
INNER JOIN sys.columns scp
ON fkc.parent_object_id = scp.object_id
AND fkc.parent_column_id =scp.column_id
INNER JOIN sys.columns scr
ON fkc.referenced_object_id = scr.object_id
AND fkc.referenced_column_id =scr.column_id
WHERE OBJECT_NAME(fkc.parent_object_id)= 'TableName'
============================================================

I have tested the queries in Adventureworks database. David, please have
a test and tell me the testing result.

works great - thanks - dave

david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm

How do I get all FK:PK mappings with the schema info?

David Thielen

David Thielen

David Thielen

Lingzhi Sun [MSFT]

David Thielen

Lingzhi Sun [MSFT]

David Thielen

Lingzhi Sun [MSFT]

David Thielen