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
Excellenet Script
Perfect
REALLY HELPED ME.. GR8 SCRIPTING..
can there be multiple primary key (in multiple tables) in response to one foreign key in a table
Hi
Thanks for your query, it set me off in the right direction :).
Much Appreciated.
I have rewritten the query to be slightly faster:
SELECT
CONSTRAINT_NAME = REF_CONST.CONSTRAINT_NAME,
TABLE_CATALOG = FK.TABLE_CATALOG,
TABLE_SCHEMA = FK.TABLE_SCHEMA,
TABLE_NAME = FK.TABLE_NAME,
COLUMN_NAME = FK_COLS.COLUMN_NAME,
REFERENCED_TABLE_CATALOG = PK.TABLE_CATALOG,
REFERENCED_TABLE_SCHEMA = PK.TABLE_SCHEMA,
REFERENCED_TABLE_NAME = PK.TABLE_NAME,
REFERENCED_COLUMN_NAME = PK_COLS.COLUMN_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS REF_CONST
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON REF_CONST.CONSTRAINT_CATALOG = FK.CONSTRAINT_CATALOG
AND REF_CONST.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA
AND REF_CONST.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
AND FK.CONSTRAINT_TYPE = ‘FOREIGN KEY’
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON REF_CONST.UNIQUE_CONSTRAINT_CATALOG = PK.CONSTRAINT_CATALOG
AND REF_CONST.UNIQUE_CONSTRAINT_SCHEMA = PK.CONSTRAINT_SCHEMA
AND REF_CONST.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
AND PK.CONSTRAINT_TYPE = ‘PRIMARY KEY’
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE FK_COLS ON REF_CONST.CONSTRAINT_NAME = FK_COLS.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE PK_COLS ON PK.CONSTRAINT_NAME = PK_COLS.CONSTRAINT_NAME
Hope this helps :)
-Mark
Thanks… It helped me lot..
The linq version is:
var foreignKeys =
from rc in datacontext.REFERENTIAL_CONSTRAINTs
join tc in datacontext.TABLE_CONSTRAINTs
on new { rc.CONSTRAINT_CATALOG, rc.CONSTRAINT_SCHEMA, rc.CONSTRAINT_NAME }
equals new { tc.CONSTRAINT_CATALOG, tc.CONSTRAINT_SCHEMA, tc.CONSTRAINT_NAME }
where tc.CONSTRAINT_TYPE == “FOREIGN KEY”
join tc2 in datacontext.TABLE_CONSTRAINTs
//on rc.UNIQUE_CONSTRAINT_CATALOG equals tc2.CONSTRAINT_CATALOG
on new { cat = rc.UNIQUE_CONSTRAINT_CATALOG, sch = rc.UNIQUE_CONSTRAINT_SCHEMA, nam = rc.UNIQUE_CONSTRAINT_NAME }
equals new { cat = tc2.CONSTRAINT_CATALOG, sch = tc2.CONSTRAINT_SCHEMA, nam = tc2.CONSTRAINT_NAME }
where tc2.CONSTRAINT_TYPE == “PRIMARY KEY”
join kcu in datacontext.KEY_COLUMN_USAGEs
on rc.CONSTRAINT_NAME equals kcu.CONSTRAINT_NAME
join kcu2 in datacontext.KEY_COLUMN_USAGEs
on rc.CONSTRAINT_NAME equals kcu2.CONSTRAINT_NAME
select new
{
CONSTRAINT_NAME = rc.CONSTRAINT_NAME,
TABLE_CATALOG = tc.TABLE_CATALOG,
TABLE_SCHEMA = tc.TABLE_SCHEMA,
TABLE_NAME = tc.TABLE_NAME,
COLUMN_NAME = kcu.COLUMN_NAME,
REFERENCED_TABLE_CATALOG = tc2.TABLE_CATALOG,
REFERENCED_TABLE_SCHEMA = tc2.TABLE_SCHEMA,
REFERENCED_TABLE_NAME = tc2.TABLE_NAME,
REFERENCED_COLUMN_NAME = kcu2.COLUMN_NAME
};
slight error, this should be the correct one:
var foreignKeys =
from REF_CONST in datacontext.REFERENTIAL_CONSTRAINTs
join FK in datacontext.TABLE_CONSTRAINTs
on new { REF_CONST.CONSTRAINT_CATALOG, REF_CONST.CONSTRAINT_SCHEMA, REF_CONST.CONSTRAINT_NAME }
equals new { FK.CONSTRAINT_CATALOG, FK.CONSTRAINT_SCHEMA, FK.CONSTRAINT_NAME }
where FK.CONSTRAINT_TYPE == “FOREIGN KEY”
join FK2 in datacontext.TABLE_CONSTRAINTs
on new { cat = REF_CONST.UNIQUE_CONSTRAINT_CATALOG, sch = REF_CONST.UNIQUE_CONSTRAINT_SCHEMA, nam = REF_CONST.UNIQUE_CONSTRAINT_NAME }
equals new { cat = FK2.CONSTRAINT_CATALOG, sch = FK2.CONSTRAINT_SCHEMA, nam = FK2.CONSTRAINT_NAME }
where FK2.CONSTRAINT_TYPE == “PRIMARY KEY”
join FK_COLS in datacontext.KEY_COLUMN_USAGEs
on REF_CONST.CONSTRAINT_NAME equals FK_COLS.CONSTRAINT_NAME
join PK_COLS in datacontext.KEY_COLUMN_USAGEs
on FK2.CONSTRAINT_NAME equals PK_COLS.CONSTRAINT_NAME
select new
{
CONSTRAINT_NAME = REF_CONST.CONSTRAINT_NAME,
TABLE_CATALOG = FK.TABLE_CATALOG,
TABLE_SCHEMA = FK.TABLE_SCHEMA,
TABLE_NAME = FK.TABLE_NAME,
COLUMN_NAME = FK_COLS.COLUMN_NAME,
REFERENCED_TABLE_CATALOG = FK2.TABLE_CATALOG,
REFERENCED_TABLE_SCHEMA = FK2.TABLE_SCHEMA,
REFERENCED_TABLE_NAME = FK2.TABLE_NAME,
REFERENCED_COLUMN_NAME = PK_COLS.COLUMN_NAME
};
ORA-00923: FROM keyword not found where expected……….
rupesh – that’s oracle error.
Hi Pinal,
Am a beginner in SQLServer and I find your script to be very helpful.
Do you have a similar script that lists all types of constraints, i.e. check constraints etc.?
Any pointers to the right direction will be very much appreciated.
Really fundoo, good that people like you believe in sharing information!
Hi Pinal,
I used this script to find duplicate relationships. Thank you for this information sharing.
Hey, this is really a good querry, We can use when we want to know that how many primary keys and forign keys are there in the database.
BUt as Subhash said that he used this querry to “find duplicate relationships” in the database.
Wht is mean by “duplicate relationships” ???
Hi,
What is the query used to display all tables names in sql server (Query analyzer) ?
Pls reply to this question? ASAP
thanks and regards
n.m.
Excellent, Very impressed
Hi there again
I was just re-looking at this script, and was wondering about something:
For the line in the script above that reads:
WHERE i1.CONSTRAINT_TYPE = ‘PRIMARY KEY’
Is this necessary, because don’t all foreign key constraints correspond to Primary Keys? In the REFERENTIAL_CONSTRAINTS table, the FK columns correspond to UNIQUE_CONSTRAINT…. columns. Can a FK correspond to any unique constraint or only a Primary Key constraint?
Thanks
Sorry, just to clarify the above comment:
The above mentioned where clause in your SQL is deftinately necessary because of the nested select statement.
Removing the CONSTRAINT_TYPE clause in my script above will not make it any faster.
I am just wondering what applies conceptually :)
hi every one
Thank you alot i am a bigenier in developing software
i want your advice to help me go in this field
Nice! However, I don’t think it’s working properly on multi-column foreign keys.
This is not a comment rather question.
I went through the above post and found very interesting. I tried to modify it according to my problem but no luck.
Problem.
how to get the list of tables who has the FK relation ship with the given tables. once i get the list I want to delete the record from the parent table and set the references to null from the table where parents pk is referred as fk.
I am not using cascade delete since I don’t want to delete the complete row.
right now i am doing this by writing the simple SP and manually setting the null to the referenced tables.
Thanks you very much in advance.
-Santosh Maskar
after spending some time on the given SQL I solved my problem.
Thanks a lot
-Santosh
hi
I need to delete the paticular records from parent table without affecting the child table(will have more child table) and also the relationship between the records should be removed,
since am going to use the unaffected record in child table as parent table..
Hope u got it..
Your help is appreciated.
Thanks & Regards
Bala
Thank for the script. It really help me.
Thank
Regards.
Johnson
This was a great tutorial! It was no problem at all to adapt this and create a routine that automatically fixed that pesky name mangling the occurs in Sql Server constraints ( so that FK__Trader__departme__4B7734FF becomes FK__Trader__department_id).
You helped me solve a real headache.