UPDATE : SQL SERVER – 2005 – Find Tables With Foreign Key Constraint in Database
This is very long query. Optionally, we can limit the query to return results for one or more than one table.
SELECT
K_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
----Â optional:
ORDERÂ BY
1,2,3,4
WHERE PK.TABLE_NAME='something'WHERE FK.TABLE_NAME='something'
WHERE PK.TABLE_NAME IN ('one_thing', 'another')
WHERE FK.TABLE_NAME IN ('one_thing', 'another')
Reference : Pinal Dave (https://blog.sqlauthority.com)
136 Comments. Leave new
Is there any query which will show all the tables which is referenced by the table Employee
Thanks
Hello Rajan,
Use sp_depends store procedure. It will list all objects that reference and that are referenced by specified table.
Regards,
Pinal Dave
Hi, I have 2 tables (Staff and Family Mem) with a one to many relationship..
In Family Mem table, one family member can belong to only one staff, while staff can have more than one family mem.
In staff table, staffid is the PK while staffid is the FK in the family table.
I would like to know the query/way to relate these two tables using foreign key if I am using XAMPP?
And how should I write the INSERT statement to insert into both tables at once since the staff’s registration form also contain data that belongs to the family mem table…
PLS help… Thxxxx so much…
Hi
I find your scripts really helpful!
i developed this shorter one from some of your codes:
SELECT K.TABLE_NAME AS TableName, K.COLUMN_NAME AS ColumnName, OBJECT_NAME(S.referenced_object_id) AS RefTableName,
COL_NAME(S.referenced_object_id, S.referenced_column_id) AS RefColumn, K.CONSTRAINT_NAME AS ConstraintName
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K INNER JOIN
sys.foreign_key_columns AS S ON K.TABLE_NAME = OBJECT_NAME(S.parent_object_id)
WHERE (K.TABLE_NAME = @TableName)
ORDER BY ConstraintName DESC
I want the PK and all FKs in the table. I get it but it returns them in duplicate lines. Can you help me narrow to single line results?
thanks
@M
Great script! I believe this is the only one mentioned here that returns the correct results for fks referencing non-primary, unique-key columns. Also it works with linked servers so I can look for missing fks on production by comparing to development.
Thanks!
This was great one!!!
Hi Pinal, this is a seriously good script and it must feel great that you have helped so many people over such a long period of time.
Thanks!
Hello,
the above script has a bug. When an FK consists of two columns, it produces a cross-product of four rows for that FK, not two rows.
I still need to analyze the query to understand and fix it.
Greetings, Christoph
Pinal,
There are a couple of corrections needed to your query; it does not handle FK’s linked through unique constraints, and it does not handle multi-column keys well. Also, there was no need for the in-line ‘pt’ table. Here is he revised 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
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE pkcu
ON rc.UNIQUE_CONSTRAINT_NAME = pkcu.CONSTRAINT_NAME
WHERE
fkcu.ORDINAL_POSITION = pkcu.ORDINAL_POSITION
Even with all the above changes, the query will not be able to handle FK relationships linked to an independantly created unique index (i.e. index created using ‘create index’ statement).
Pinal ,
how do i find the relation ship between attributes in a table ,
i need to write a query which runs on a table
(table name will be passed as the parameter to the query )
and tel me the relationship between the primary key column and the other columns in the same table , result should tell me the relationship is whether its 1:1 or 1:m or M:1 or M:M taking primary key as the granular level
Regards,
Ashok
Start with
EXEC sp_help ‘table_name’
Thank you Dave, you’re now one of my heroes !!
Terrific script, helped me a lot modeling an existing DB. Thanks for sharing!
for the scripts below, where do i put my table name and colum names
select object_name(constid) FKey_Name, object_name(fkeyid) Child_Table, c1.name FKey_Col,
object_name(rkeyid) Parent_Table, c2.name Ref_KeyCol
from sysforeignkeys s
inner join syscolumns c1
on ( s.fkeyid = c1.id
and s.fkey = c1.colid )
inner join syscolumns c2
on ( s.rkeyid = c2.id
and s.rkey = c2.colid )
Edit: Added constraint type
select tbl.constraint_name
, tbl.constraint_type
, tbl.table_schema
, tbl.table_name
, col.column_name
, col3.table_schema as ref_table_name
, col3.table_name as ref_table_name
, col3.column_name as ref_column_name
from information_schema.table_constraints tbl with(nolock)
left join information_schema.constraint_column_usage col with(nolock) on tbl.constraint_name = col.constraint_name
left join information_schema.referential_constraints col2 with(nolock) on tbl.constraint_name = col2.constraint_name
left join information_schema.constraint_column_usage col3 with(nolock) on col2.unique_constraint_name = col3.constraint_name
order by tbl.table_name
thanks for all Examples!
pretty good
hi guys….
create table emp
(e_id int primary key,
e_name varchar(255))
insert table emp values(1,’aa’);
insert table emp values(2,’bb’)
then i created new table:
create table dept
(d_id int primary key,
d_name varchar(255),
e_id int foreign key references emp(e_id))
with this st. it works well:
insert table dept values(11,’computer’,1);
this st. creats problem:
insert table dept values(33,’marketing’,3);
so plz help on this problem
error for the above problem:
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint “FK__t22__e_id__4FD1D5C8”. The conflict occurred in database “master”, table “dbo.t11”, column ‘e_id’.
The statement has been terminated.
It is becuase you didn’t have e_id 3 in the emp table
hi
madhivanan,
i know that ,but my question is i have created one table with primary column but when i create another table with foreign key of references to first table primary column. when i insert data it does not except it and give above error….
plz let me know were i m going wrong
thanks in advances
Excellent query, exactly what I needed – thanks!
Very handy ! Thanks!
– Ferruccio Guicciardi