Prev: Get all FKs with their table names, PK/FK fields
Next: What's the next release after SQL Server 2008 R2 & when?
From: Emiel on 21 Jun 2010 04:13
Upon closer inspection the query returns too many results since the ordinal position of the field is not taken into account.
The following SQL is correct:

SELECT
FK_Table = fk.table_name,
FK_Column = fkc.column_name,
PK_Table = pk.table_name,
PK_Column = pkc.column_name,
constraint_name = fk.constraint_name
FROM information_schema.referential_constraints c
JOIN information_schema.table_constraints fk
ON c.constraint_name = fk.constraint_name
JOIN information_schema.table_constraints pk
ON c.unique_constraint_name = pk.constraint_name
JOIN information_schema.key_column_usage fkc
ON fk.constraint_name = fkc.constraint_name
JOIN information_schema.key_column_usage pkc
ON pk.constraint_name = pkc.constraint_name
AND fkc.ordinal_position = pkc.ordinal_position
WHERE pk.constraint_type = 'PRIMARY KEY'
ORDER BY 1,2,3,4




Emiel Nijhuis wrote:

Get all FKs with their table names, PK/FK fields
01-May-10

Check out:

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/c33aa736-3303-4968-b1fb-8308d935148f

Previous Posts In This Thread:

On Saturday, October 03, 2009 4:46 AM
Muhammad Bilal wrote:

Table & Column Name
Hi.

How can i know that a column of a table is in which table as foriegn key &
whats tha name of that foriegn key clun in the other table.


Let if i have a table1 with col1. I want to know that the col1 is in which
table as foreign key column and the name of the that column.


Regards,
Muhammad Bilal

On Saturday, October 03, 2009 9:38 AM
Arif wrote:

RE: Table & Column Name
"Muhammad Bilal" wrote:


try this


select object_name(constraint_object_id) 'constraint name',
object_name(parent_object_id ) 'parent table name',
object_name(referenced_object_id) 'referenced table name'
from sys.foreign_key_columns
where object_name(parent_object_id ) = 'TableName'

On Saturday, October 03, 2009 10:00 AM
Arif wrote:

RE: Table & Column Name
following script will give you the clumn names as well

"Muhammad Bilal" wrote:

On Saturday, October 03, 2009 10:04 AM
Arif wrote:

following script will give you the parent table, referenced table ,
following script will give you the parent table, referenced table ,
constraint name and the clumn names

select object_name(constraint_object_id) 'constraint name',
object_name(parent_object_id ) 'parent table name',
object_name(referenced_object_id) 'referenced table name',
c1.name 'parent column name',
c2.name 'referenced column name'
from sys.foreign_key_columns f
inner join sys.columns c1 on f.parent_column_id = c1.column_id and
c1.object_id = f.parent_object_id
inner join sys.columns c2 on f.referenced_column_id = c2.column_id and
c2.object_id = f.referenced_object_id
where object_name(parent_object_id ) = 'yourtable'



"Muhammad Bilal" wrote:

On Saturday, October 03, 2009 1:50 PM
Erland Sommarskog wrote:

Re: Table & Column Name
Muhammad Bilal (MuhammadBilal(a)discussions.microsoft.com) writes:

Not sure that I understand what you are looking for, but this little
script should give you started:

create table first (a int NOT NULL PRIMARY KEY,
b int NOT NULL,
c int NOT NULL)

create table second (a int NOT NULL,
b int NOT NULL,
c int NOT NULL,
PRIMARY KEY (a, b))

create table third (a int NOT NULL PRIMARY KEY,
b int NOT NULL REFERENCES first(a),
c int NOT NULL,
d int NOT NULL,
CONSTRAINT fk2 FOREIGN KEY (c, d)
REFERENCES second(a, b))
go
SELECT srctbl = o1.name, srccol = c1.name,
targettbl = o2.name, targetcol = c2.name
FROM sys.objects o1
JOIN sys.columns c1 ON o1.object_id = c1.object_id
JOIN sys.foreign_key_columns fkk ON fkk.parent_object_id = o1.object_id
AND fkk.parent_column_id = c1.column_id
JOIN sys.objects o2 ON o2.object_id = fkk.referenced_object_id
JOIN sys.columns c2 ON o2.object_id = c2.object_id
AND fkk.referenced_column_id = c2.column_id
ORDER BY o1.name, o2.name, c1.name, c2.name
go
drop table third, second, first



--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

On Saturday, May 01, 2010 3:04 AM
Emiel Nijhuis wrote:

Get all FKs with their table names, PK/FK fields
Check out:

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/c33aa736-3303-4968-b1fb-8308d935148f

On Monday, June 21, 2010 4:07 AM
Emiel Nijhuis wrote:

Get all FKs with their table names, PK/FK fields
Upon closer inspection your query returns too many results since the ordinal position of the field is not taken into account.
The following SQL is correct:

SELECT
FK_Table = fk.table_name,
FK_Column = fkc.column_name,
PK_Table = pk.table_name,
PK_Column = pkc.column_name,
constraint_name = fk.constraint_name
FROM information_schema.referential_constraints c
JOIN information_schema.table_constraints fk
ON c.constraint_name = fk.constraint_name
JOIN information_schema.table_constraints pk
ON c.unique_constraint_name = pk.constraint_name
JOIN information_schema.key_column_usage fkc
ON fk.constraint_name = fkc.constraint_name
JOIN information_schema.key_column_usage pkc
ON pk.constraint_name = pkc.constraint_name
AND fkc.ordinal_position = pkc.ordinal_position
WHERE pk.constraint_type = 'PRIMARY KEY'
ORDER BY 1,2,3,4


Submitted via EggHeadCafe - Software Developer Portal of Choice
NoSQL, MongoDB Install, Lotus Notes, and CouchDB
http://www.eggheadcafe.com/tutorials/aspnet/63de8012-127a-4478-8725-3e1c27969596/nosql-mongodb-install-lotus-notes-and-couchdb.aspx
 | 
Pages: 1
Prev: Get all FKs with their table names, PK/FK fields
Next: What's the next release after SQL Server 2008 R2 & when?