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
Thanks Dave, very handy, specially when putting together ETL process to populate Data Warehouses (Kimball models).
All of these scripts are wrong.
You are assuming the foreign key relationships link to a primary key field. It is quite possible that a foreign key relationship link to an index with a unique constraint.
Here is a working script:
SELECT ForeignKeys.name [ForeignKeyName], PrimaryKeyTable.name [PrimaryTableName], PrimaryKeyColumn.name [PrimaryColumnName],
ForeignKeyTable.name [ReferenceTableName], ForeignKeyColumn.name [ReferenceColumnName],
ForeignKeys.update_referential_action_desc [UpdateAction], ForeignKeys.delete_referential_action_desc [DeleteAction]
FROM sys.foreign_keys ForeignKeys
JOIN sys.foreign_key_columns ForeignKeyRelationships ON (ForeignKeys.object_id = ForeignKeyRelationships.constraint_object_id)
JOIN sys.tables ForeignKeyTable ON ForeignKeyRelationships.parent_object_id = ForeignKeyTable.object_id
JOIN sys.columns ForeignKeyColumn ON (ForeignKeyTable.object_id = ForeignKeyColumn.object_id AND ForeignKeyRelationships.parent_column_id = ForeignKeyColumn.column_id)
JOIN sys.tables PrimaryKeyTable ON ForeignKeyRelationships.referenced_object_id = PrimaryKeyTable.object_id
JOIN sys.columns PrimaryKeyColumn ON (PrimaryKeyTable.object_id = PrimaryKeyColumn.object_id AND ForeignKeyRelationships.referenced_column_id = PrimaryKeyColumn.column_id)
ORDER BY ForeignKeys.name
Excellent Pinal ..Always I used to search your posts by thinking that I will get a perfect Answer…
Hi
I have a MySQL database that consists of 40 tables and is located on.
My knowledge of the database that is on a piece of code I am not able to find all Relationships database.
When using the following code fragment shows an error message .
Piece of code :
SELECT
Table_Name = FK.TABLE_NAME,
Primary_Key = PT.COLUMN_NAME,
Foreign_Key = CU.COLUMN_NAME,
PK_Table = PK.TABLE_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
Error message :
#1052 – Column ‘Table_Name’ in field list is ambiguous
Please use the code please ?
Thanks
the name table_name is part of more than two tables. You need to qualify it with table alias name like pk.table_name
Hi. Thanks for all your help over the years by the way. One small comment on the order by. To make it relational you should not use the ordinal position of the column, you should use the column name or alias.
great script. i use several scripts from this site which are extremely helpful!
thanks!
Hi!
I was searching a similar query, but yours is not working if the FK is referring to a non primary-key or if there are multiple columns in it.
I leave my solution here, if
SELECT DISTINCT
tc.constraint_name,
tc.constraint_type,
tc.table_name,
kcu.column_name,
tc.is_deferrable,
tc.initially_deferred,
rc.match_option AS match_type,
rc.update_rule AS on_update,
rc.delete_rule AS on_delete,
ccu.table_name AS references_table,
ccu.column_name AS references_field
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
ON tc.constraint_catalog = rc.constraint_catalog
AND tc.constraint_schema = rc.constraint_schema
AND tc.constraint_name = rc.constraint_name
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
ON tc.constraint_catalog = kcu.constraint_catalog
aND tc.constraint_schema = kcu.constraint_schema
AND tc.constraint_name = kcu.constraint_name
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ccu
ON rc.unique_constraint_catalog = ccu.constraint_catalog
AND rc.unique_constraint_schema = ccu.constraint_schema
AND rc.unique_constraint_name = ccu.constraint_name
AND kcu.ordinal_position = ccu.ordinal_position
WHERE tc.constraint_type = ‘FOREIGN KEY’
AND tc.table_name = ‘some_table_name’
AND tc.constraint_name = ‘some_fk_name’
AND tc.constraint_catalog = ‘some_catalog_name’
Of course, configure the WHERE with your own conditions.
And thank you for the initial share, which gave me the start way to find my solution.
SELECT PKTABLE_QUALIFIER = CONVERT(SYSNAME,DB_NAME()),
PKTABLE_OWNER = CONVERT(SYSNAME,SCHEMA_NAME(O1.SCHEMA_ID)),
PKTABLE_NAME = CONVERT(SYSNAME,O1.NAME),
PKCOLUMN_NAME = CONVERT(SYSNAME,C1.NAME),
FKTABLE_QUALIFIER = CONVERT(SYSNAME,DB_NAME()),
FKTABLE_OWNER = CONVERT(SYSNAME,SCHEMA_NAME(O2.SCHEMA_ID)),
FKTABLE_NAME = CONVERT(SYSNAME,O2.NAME),
FKCOLUMN_NAME = CONVERT(SYSNAME,C2.NAME),
— Force the column to be non-nullable (see SQL BU 325751)
–KEY_SEQ = isnull(convert(smallint,k.constraint_column_id), sysconv(smallint,0)),
UPDATE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.OBJECT_ID,’CnstIsUpdateCascade’)
WHEN 1 THEN 0
ELSE 1
END),
DELETE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.OBJECT_ID,’CnstIsDeleteCascade’)
WHEN 1 THEN 0
ELSE 1
END),
FK_NAME = CONVERT(SYSNAME,OBJECT_NAME(F.OBJECT_ID)),
PK_NAME = CONVERT(SYSNAME,I.NAME),
DEFERRABILITY = CONVERT(SMALLINT,7) — SQL_NOT_DEFERRABLE
FROM SYS.ALL_OBJECTS O1,
SYS.ALL_OBJECTS O2,
SYS.ALL_COLUMNS C1,
SYS.ALL_COLUMNS C2,
SYS.FOREIGN_KEYS F
INNER JOIN SYS.FOREIGN_KEY_COLUMNS K
ON (K.CONSTRAINT_OBJECT_ID = F.OBJECT_ID)
INNER JOIN SYS.INDEXES I
ON (F.REFERENCED_OBJECT_ID = I.OBJECT_ID
AND F.KEY_INDEX_ID = I.INDEX_ID)
WHERE O1.OBJECT_ID = F.REFERENCED_OBJECT_ID
AND O2.OBJECT_ID = F.PARENT_OBJECT_ID
AND C1.OBJECT_ID = F.REFERENCED_OBJECT_ID
AND C2.OBJECT_ID = F.PARENT_OBJECT_ID
AND C1.COLUMN_ID = K.REFERENCED_COLUMN_ID
AND C2.COLUMN_ID = K.PARENT_COLUMN_ID
Hi Pinal,
I use this to get most of the needed metadata if I don’t have access to the Diagram
select ic.TABLE_CATALOG
,ic.TABLE_NAME
,ic.COLUMN_NAME
,ic.IS_NULLABLE
,ic.DATA_TYPE
,ic.CHARACTER_MAXIMUM_LENGTH
,it.CONSTRAINT_NAME
,rf.UPDATE_RULE
,rf.DELETE_RULE
from information_schema.columns ic
left join information_schema.KEY_COLUMN_USAGE it
left join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rf on it.CONSTRAINT_NAME = rf.CONSTRAINT_NAME
on it.COLUMN_NAME=ic.COLUMN_NAME
where
it.TABLE_NAME = ic.TABLE_NAME or it.TABLE_NAME is null
order by ic.TABLE_NAME, it.CONSTRAINT_NAME desc, ic.COLUMN_NAME;
Hi,
This is really great….
One thing I notice is first column must be FK_Table I guess…
Welcome @Riya
Question: I have more tables in my database than the query resulted. There is a FK I specifically wanted to find the relations to but the table in which it is a FK didn’t even show up in results. Is there a restriction or constraint in the query?
Sorry if this sounds too silly. I am a beginner.
Thanks!
Very Nice Script
Thanks @Alvaro
CREATE TABLE `student_history`(
`student_history_id` int(11) not null AUTO_INCREMENT,
`student_id` int(11) unsigned not null ,
`class_id` int(11) unsigned not null,
`section_id` int(11) unsigned not null,
`roll_number` int(15) not null,
`admission_year` date() not null,
PRIMARY KEY(`student_history_id`),
CONSTRAINT `fk_student` FOREIGN KEY (`student_id`)
REFERENCES `student`(`student_id`),
CONSTRAINT `fk_class` FOREIGN KEY (`class_id`)
REFERENCES `classes`(`class_id`),
CONSTRAINT `fk_section` FOREIGN KEY (`section_id`)
REFERENCES `section`(`section_id`)
)ENGINE=INNODB charset=utf8;
my query is not executed.
One thing is missing from this function. The number of rows that exists for each relations. How could it be added? I’ve been looking for a solution for a while, with no results.
Very helpful.