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

D

David Thielen

Hi;

I need to get all FK:pK 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
 
D

David Thielen

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 FK:pK 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
Click to expand...


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

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

Lingzhi Sun [MSFT]

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.
 
D

David Thielen

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.
...
Click to expand...

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:pK 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:pK 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
 
L

Lingzhi Sun [MSFT]

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.
=================================================
 
D

David Thielen

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?
...
Click to expand...

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
 
L

Lingzhi Sun [MSFT]

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.
=================================================
 
D

David Thielen

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.
Click to expand...

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to get columns from a schema.table? 3
How do I get all column descriptions from a schema.table? 7

Top