Find the Foreign Keys Associated with a Given Primary Key
I want a way to establish which columns in a given database are joined via PK/FK relationships. I can return the PK/FK information for a given table via
SELECT *
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS cu
WHERE EXISTS (
SELECT tc.*
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
WHERE tc.CONSTRAINT_CATALOG = 'MyDatabase'
AND tc.TABLE_NAME = 'MyTable'
/*AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'*/
AND tc.CONSTRAINT_NAME = cu.CONSTRAINT_NAME);
GO
but for a PK returned from such a query how do I establish the associated FK (assuming there is one)?
I know you can also get the referenced tables via:
SELECT CONSTRAINT_NAME = name,
FOREIGN_SCHEMA = OBJECT_SCHEMA_NAME(parent_object_id),
FOREIGN_TABLE = OBJECT_NAME(parent_object_id),
REFERENCED_SCHEMA = OBJECT_SCHEMA_NAME(referenced_object_id),
REFERENCED_TABLE = OBJECT_NAME(referenced_object_id)
FROM sys.foreign_keys
WHERE OBJECT_NAME(referenced_object_id) = 'MyTable';
GO
but I am struggling now to get the explicit column references.
I am creating a script generator for QlikView. To generate the script I need the constraints and the associated links. I need all of the constraint information for any given column (if any).
I want to construct a database class that holds all the information for a given database. This class structure database.table.column.constraints will then be used to get the matches between different columns on PK/FKs.
Clearly some columns will have FKs only and in this case I also want to retrieve the PK information of the corresponding key; some will have only PKs and then I want the reverse. Some of course can have both.
sql-server foreign-key metadata
edited Nov 1 '17 at 4:41
Paul White♦
49.5k14261415
asked Jan 10 '13 at 18:42
MoonKnightMoonKnight
2371412
add a comment |
I want a way to establish which columns in a given database are joined via PK/FK relationships. I can return the PK/FK information for a given table via
SELECT *
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS cu
WHERE EXISTS (
SELECT tc.*
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
WHERE tc.CONSTRAINT_CATALOG = 'MyDatabase'
AND tc.TABLE_NAME = 'MyTable'
/*AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'*/
AND tc.CONSTRAINT_NAME = cu.CONSTRAINT_NAME);
GO
but for a PK returned from such a query how do I establish the associated FK (assuming there is one)?
I know you can also get the referenced tables via:
SELECT CONSTRAINT_NAME = name,
FOREIGN_SCHEMA = OBJECT_SCHEMA_NAME(parent_object_id),
FOREIGN_TABLE = OBJECT_NAME(parent_object_id),
REFERENCED_SCHEMA = OBJECT_SCHEMA_NAME(referenced_object_id),
REFERENCED_TABLE = OBJECT_NAME(referenced_object_id)
FROM sys.foreign_keys
WHERE OBJECT_NAME(referenced_object_id) = 'MyTable';
GO
but I am struggling now to get the explicit column references.
I am creating a script generator for QlikView. To generate the script I need the constraints and the associated links. I need all of the constraint information for any given column (if any).
I want to construct a database class that holds all the information for a given database. This class structure database.table.column.constraints will then be used to get the matches between different columns on PK/FKs.
Clearly some columns will have FKs only and in this case I also want to retrieve the PK information of the corresponding key; some will have only PKs and then I want the reverse. Some of course can have both.
sql-server foreign-key metadata
edited Nov 1 '17 at 4:41
Paul White♦
49.5k14261415
asked Jan 10 '13 at 18:42
MoonKnightMoonKnight
2371412
add a comment |
I want a way to establish which columns in a given database are joined via PK/FK relationships. I can return the PK/FK information for a given table via
SELECT *
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS cu
WHERE EXISTS (
SELECT tc.*
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
WHERE tc.CONSTRAINT_CATALOG = 'MyDatabase'
AND tc.TABLE_NAME = 'MyTable'
/*AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'*/
AND tc.CONSTRAINT_NAME = cu.CONSTRAINT_NAME);
GO
but for a PK returned from such a query how do I establish the associated FK (assuming there is one)?
I know you can also get the referenced tables via:
SELECT CONSTRAINT_NAME = name,
FOREIGN_SCHEMA = OBJECT_SCHEMA_NAME(parent_object_id),
FOREIGN_TABLE = OBJECT_NAME(parent_object_id),
REFERENCED_SCHEMA = OBJECT_SCHEMA_NAME(referenced_object_id),
REFERENCED_TABLE = OBJECT_NAME(referenced_object_id)
FROM sys.foreign_keys
WHERE OBJECT_NAME(referenced_object_id) = 'MyTable';
GO
but I am struggling now to get the explicit column references.
I am creating a script generator for QlikView. To generate the script I need the constraints and the associated links. I need all of the constraint information for any given column (if any).
I want to construct a database class that holds all the information for a given database. This class structure database.table.column.constraints will then be used to get the matches between different columns on PK/FKs.
Clearly some columns will have FKs only and in this case I also want to retrieve the PK information of the corresponding key; some will have only PKs and then I want the reverse. Some of course can have both.
sql-server foreign-key metadata
edited Nov 1 '17 at 4:41
Paul White♦
49.5k14261415
asked Jan 10 '13 at 18:42
MoonKnightMoonKnight
2371412
I want a way to establish which columns in a given database are joined via PK/FK relationships. I can return the PK/FK information for a given table via
SELECT *
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS cu
WHERE EXISTS (
SELECT tc.*
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
WHERE tc.CONSTRAINT_CATALOG = 'MyDatabase'
AND tc.TABLE_NAME = 'MyTable'
/*AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'*/
AND tc.CONSTRAINT_NAME = cu.CONSTRAINT_NAME);
GO
but for a PK returned from such a query how do I establish the associated FK (assuming there is one)?
I know you can also get the referenced tables via:
SELECT CONSTRAINT_NAME = name,
FOREIGN_SCHEMA = OBJECT_SCHEMA_NAME(parent_object_id),
FOREIGN_TABLE = OBJECT_NAME(parent_object_id),
REFERENCED_SCHEMA = OBJECT_SCHEMA_NAME(referenced_object_id),
REFERENCED_TABLE = OBJECT_NAME(referenced_object_id)
FROM sys.foreign_keys
WHERE OBJECT_NAME(referenced_object_id) = 'MyTable';
GO
but I am struggling now to get the explicit column references.
I am creating a script generator for QlikView. To generate the script I need the constraints and the associated links. I need all of the constraint information for any given column (if any).
I want to construct a database class that holds all the information for a given database. This class structure database.table.column.constraints will then be used to get the matches between different columns on PK/FKs.
Clearly some columns will have FKs only and in this case I also want to retrieve the PK information of the corresponding key; some will have only PKs and then I want the reverse. Some of course can have both.
sql-server foreign-key metadata
sql-server foreign-key metadata
edited Nov 1 '17 at 4:41
Paul White♦
49.5k14261415
asked Jan 10 '13 at 18:42
MoonKnightMoonKnight
2371412
edited Nov 1 '17 at 4:41
Paul White♦
49.5k14261415
asked Jan 10 '13 at 18:42
MoonKnightMoonKnight
2371412
edited Nov 1 '17 at 4:41
Paul White♦
49.5k14261415
edited Nov 1 '17 at 4:41
Paul White♦
49.5k14261415
edited Nov 1 '17 at 4:41
Paul White♦
49.5k14261415
49.5k14261415
asked Jan 10 '13 at 18:42
MoonKnightMoonKnight
2371412
asked Jan 10 '13 at 18:42
MoonKnightMoonKnight
2371412
asked Jan 10 '13 at 18:42
MoonKnightMoonKnight
2371412
2371412
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
Here's a simple query to match up foreign keys to their referenced tables/columns:
SELECT
o1.name AS FK_table,
c1.name AS FK_column,
fk.name AS FK_name,
o2.name AS PK_table,
c2.name AS PK_column,
pk.name AS PK_name,
fk.delete_referential_action_desc AS Delete_Action,
fk.update_referential_action_desc AS Update_Action
FROM sys.objects o1
INNER JOIN sys.foreign_keys fk
ON o1.object_id = fk.parent_object_id
INNER JOIN sys.foreign_key_columns fkc
ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.columns c1
ON fkc.parent_object_id = c1.object_id
AND fkc.parent_column_id = c1.column_id
INNER JOIN sys.columns c2
ON fkc.referenced_object_id = c2.object_id
AND fkc.referenced_column_id = c2.column_id
INNER JOIN sys.objects o2
ON fk.referenced_object_id = o2.object_id
INNER JOIN sys.key_constraints pk
ON fk.referenced_object_id = pk.parent_object_id
AND fk.key_index_id = pk.unique_index_id
ORDER BY o1.name, o2.name, fkc.constraint_column_id
The output has eight columns: the table and column names for the foreign keys (FK_table, FK_column), the names of the foreign-key constraints (FK_name), the referenced PK or unique index table and column names (PK_table, PK_column), the name of the referenced PK or unique index (PK_name), and the update/delete cascade actions (Delete_Action, Update_Action).
(Edited to add some more output columns.)
EDIT: I'm back 6 years later with an improved version of this. I realized that the original query doesn't really handle multi-column foreign keys well, and I also wanted to be able to quickly identify disabled, untrusted, or unindexed foreign keys. So here's the new version that corrects all of that.
Multi-column keys are shown as comma-separated lists in FK_columns and PK_columns, using the traditional FOR XML/STUFF abuse. The FK_indexes column shows the names of any indexes on the foreign-key table that could potentially be used to satisfy seeks using the foreign-key columns (mainly for optimizing deletes or updates to the primary key table). If it's NULL, then you've got an unindexed foreign key. You can tweak the ORDER BY, or add a WHERE clause (commented out below) if you want to sort by the PK table name, filter for specific PK/FK tables, etc.
SELECT
fk.is_disabled,
fk.is_not_trusted,
OBJECT_SCHEMA_NAME(o1.object_id) AS FK_schema,
o1.name AS FK_table,
--Generate list of columns in referring side of foreign key
STUFF(
(
SELECT ', ' + c1.name AS [text()]
FROM sys.columns c1 INNER
JOIN sys.foreign_key_columns fkc
ON c1.object_id = fkc.parent_object_id
AND c1.column_id = fkc.parent_column_id
WHERE fkc.constraint_object_id = fk.object_id
FOR XML PATH('')
), 1, 2, '') AS FK_columns,
--Look for any indexes that will fully satisfy the foreign key columns
STUFF(
(
SELECT ', ' + i.name AS [text()]
FROM sys.indexes i
WHERE i.object_id = o1.object_id
AND NOT EXISTS ( --Find foreign key columns that don't match the index key columns
SELECT fkc.constraint_column_id, fkc.parent_column_id
FROM sys.foreign_key_columns fkc
WHERE fkc.constraint_object_id = fk.object_id
EXCEPT
SELECT ic.key_ordinal, ic.column_id
FROM sys.index_columns ic
WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id
)
FOR XML PATH('')
), 1, 2, '') AS FK_indexes,
fk.name AS FK_name,
OBJECT_SCHEMA_NAME(o2.object_id) AS PK_schema,
o2.name AS PK_table,
--Generate list of columns in referenced (i.e. PK) side of foreign key
STUFF(
(
SELECT ', ' + c2.name AS [text()]
FROM sys.columns c2
INNER JOIN sys.foreign_key_columns fkc
ON c2.object_id = fkc.referenced_object_id
AND c2.column_id = fkc.referenced_column_id
WHERE fkc.constraint_object_id = fk.object_id
FOR XML PATH('')
), 1, 2, '') AS PK_columns,
pk.name AS PK_name,
fk.delete_referential_action_desc AS Delete_Action,
fk.update_referential_action_desc AS Update_Action
FROM sys.objects o1
INNER JOIN sys.foreign_keys fk
ON o1.object_id = fk.parent_object_id
INNER JOIN sys.objects o2
ON fk.referenced_object_id = o2.object_id
INNER JOIN sys.key_constraints pk
ON fk.referenced_object_id = pk.parent_object_id
AND fk.key_index_id = pk.unique_index_id
--WHERE o2.name = 'Company_Address'
ORDER BY o1.name, o2.name
answered Jan 10 '13 at 19:06
db2db2
8,01812448
add a comment |
This query nets you all of the FK relationships in the database - FK constraint name, schema/table of referencing table, referencing column name, schema/table of referenced table, and referenced column name. There will be multiple rows for a multi-column constraint.
SELECT
FK = OBJECT_NAME(pt.constraint_object_id),
Referencing_table = QUOTENAME(OBJECT_SCHEMA_NAME(pt.parent_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(pt.parent_object_id)),
Referencing_col = QUOTENAME(pc.name),
Referenced_table = QUOTENAME(OBJECT_SCHEMA_NAME(pt.referenced_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(pt.referenced_object_id)),
Referenced_col = QUOTENAME(rc.name)
FROM sys.foreign_key_columns AS pt
INNER JOIN sys.columns AS pc
ON pt.parent_object_id = pc.[object_id]
AND pt.parent_column_id = pc.column_id
INNER JOIN sys.columns AS rc
ON pt.referenced_column_id = rc.column_id
AND pt.referenced_object_id = rc.[object_id]
ORDER BY Referencing_table, FK, pt.constraint_column_id;
If you are after the columns from a specific primary key constraint, and you already know the name of that PK constraint, you can write this:
DECLARE @PK_Constraint SYSNAME = N'Name of PK constraint';
SELECT
FK = OBJECT_NAME(fkc.constraint_object_id),
Referencing_table = QUOTENAME(OBJECT_SCHEMA_NAME(fkc.parent_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(fkc.parent_object_id)),
Referencing_col = QUOTENAME(pc.name),
Referenced_table = QUOTENAME(OBJECT_SCHEMA_NAME(fkc.referenced_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(fkc.referenced_object_id)),
Referenced_col = QUOTENAME(rc.name)
FROM sys.foreign_key_columns AS fkc
INNER JOIN sys.columns AS pc
ON fkc.parent_object_id = pc.[object_id]
AND fkc.parent_column_id = pc.column_id
INNER JOIN sys.columns AS rc
ON fkc.referenced_column_id = rc.column_id
AND fkc.referenced_object_id = rc.[object_id]
WHERE EXISTS
(
SELECT 1 FROM sys.indexes AS i
INNER JOIN sys.foreign_keys AS fk
ON i.[object_id] = fk.referenced_object_id
AND i.index_id = fk.key_index_id
AND fk.[object_id] = fkc.constraint_object_id
AND i.name = @PK_Constraint
)
ORDER BY Referencing_table, FK, fkc.constraint_column_id;
If you just want to include the PK name along with the other information:
SELECT
FK = OBJECT_NAME(fkc.constraint_object_id),
Referencing_table = QUOTENAME(OBJECT_SCHEMA_NAME(fkc.parent_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(fkc.parent_object_id)),
Referencing_col = QUOTENAME(pc.name),
Referenced_table = QUOTENAME(OBJECT_SCHEMA_NAME(fkc.referenced_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(fkc.referenced_object_id)),
Referenced_col = QUOTENAME(rc.name),
PK = pk.name
FROM sys.foreign_key_columns AS fkc
INNER JOIN sys.columns AS pc
ON fkc.parent_object_id = pc.[object_id]
AND fkc.parent_column_id = pc.column_id
INNER JOIN sys.columns AS rc
ON fkc.referenced_column_id = rc.column_id
AND fkc.referenced_object_id = rc.[object_id]
INNER JOIN (SELECT i.name, fk.[object_id]
FROM sys.indexes AS i
INNER JOIN sys.foreign_keys AS fk
ON i.[object_id] = fk.referenced_object_id
AND i.index_id = fk.key_index_id
) AS pk
ON pk.[object_id] = fkc.constraint_object_id
ORDER BY Referencing_table, FK, fkc.constraint_column_id;
There are also tricks to getting the column list in, say, a comma-separated list or individual columns, instead of being spread across rows, but I'm not going to invest in modifying these queries to produce that until I know exactly which form you're after.
answered Jan 10 '13 at 19:13
Aaron Bertrand♦Aaron Bertrand
150k18284482
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f31720%2ffind-the-foreign-keys-associated-with-a-given-primary-key%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
Here's a simple query to match up foreign keys to their referenced tables/columns:
SELECT
o1.name AS FK_table,
c1.name AS FK_column,
fk.name AS FK_name,
o2.name AS PK_table,
c2.name AS PK_column,
pk.name AS PK_name,
fk.delete_referential_action_desc AS Delete_Action,
fk.update_referential_action_desc AS Update_Action
FROM sys.objects o1
INNER JOIN sys.foreign_keys fk
ON o1.object_id = fk.parent_object_id
INNER JOIN sys.foreign_key_columns fkc
ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.columns c1
ON fkc.parent_object_id = c1.object_id
AND fkc.parent_column_id = c1.column_id
INNER JOIN sys.columns c2
ON fkc.referenced_object_id = c2.object_id
AND fkc.referenced_column_id = c2.column_id
INNER JOIN sys.objects o2
ON fk.referenced_object_id = o2.object_id
INNER JOIN sys.key_constraints pk
ON fk.referenced_object_id = pk.parent_object_id
AND fk.key_index_id = pk.unique_index_id
ORDER BY o1.name, o2.name, fkc.constraint_column_id
The output has eight columns: the table and column names for the foreign keys (FK_table, FK_column), the names of the foreign-key constraints (FK_name), the referenced PK or unique index table and column names (PK_table, PK_column), the name of the referenced PK or unique index (PK_name), and the update/delete cascade actions (Delete_Action, Update_Action).
(Edited to add some more output columns.)
EDIT: I'm back 6 years later with an improved version of this. I realized that the original query doesn't really handle multi-column foreign keys well, and I also wanted to be able to quickly identify disabled, untrusted, or unindexed foreign keys. So here's the new version that corrects all of that.
Multi-column keys are shown as comma-separated lists in FK_columns and PK_columns, using the traditional FOR XML/STUFF abuse. The FK_indexes column shows the names of any indexes on the foreign-key table that could potentially be used to satisfy seeks using the foreign-key columns (mainly for optimizing deletes or updates to the primary key table). If it's NULL, then you've got an unindexed foreign key. You can tweak the ORDER BY, or add a WHERE clause (commented out below) if you want to sort by the PK table name, filter for specific PK/FK tables, etc.
SELECT
fk.is_disabled,
fk.is_not_trusted,
OBJECT_SCHEMA_NAME(o1.object_id) AS FK_schema,
o1.name AS FK_table,
--Generate list of columns in referring side of foreign key
STUFF(
(
SELECT ', ' + c1.name AS [text()]
FROM sys.columns c1 INNER
JOIN sys.foreign_key_columns fkc
ON c1.object_id = fkc.parent_object_id
AND c1.column_id = fkc.parent_column_id
WHERE fkc.constraint_object_id = fk.object_id
FOR XML PATH('')
), 1, 2, '') AS FK_columns,
--Look for any indexes that will fully satisfy the foreign key columns
STUFF(
(
SELECT ', ' + i.name AS [text()]
FROM sys.indexes i
WHERE i.object_id = o1.object_id
AND NOT EXISTS ( --Find foreign key columns that don't match the index key columns
SELECT fkc.constraint_column_id, fkc.parent_column_id
FROM sys.foreign_key_columns fkc
WHERE fkc.constraint_object_id = fk.object_id
EXCEPT
SELECT ic.key_ordinal, ic.column_id
FROM sys.index_columns ic
WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id
)
FOR XML PATH('')
), 1, 2, '') AS FK_indexes,
fk.name AS FK_name,
OBJECT_SCHEMA_NAME(o2.object_id) AS PK_schema,
o2.name AS PK_table,
--Generate list of columns in referenced (i.e. PK) side of foreign key
STUFF(
(
SELECT ', ' + c2.name AS [text()]
FROM sys.columns c2
INNER JOIN sys.foreign_key_columns fkc
ON c2.object_id = fkc.referenced_object_id
AND c2.column_id = fkc.referenced_column_id
WHERE fkc.constraint_object_id = fk.object_id
FOR XML PATH('')
), 1, 2, '') AS PK_columns,
pk.name AS PK_name,
fk.delete_referential_action_desc AS Delete_Action,
fk.update_referential_action_desc AS Update_Action
FROM sys.objects o1
INNER JOIN sys.foreign_keys fk
ON o1.object_id = fk.parent_object_id
INNER JOIN sys.objects o2
ON fk.referenced_object_id = o2.object_id
INNER JOIN sys.key_constraints pk
ON fk.referenced_object_id = pk.parent_object_id
AND fk.key_index_id = pk.unique_index_id
--WHERE o2.name = 'Company_Address'
ORDER BY o1.name, o2.name
answered Jan 10 '13 at 19:06
db2db2
8,01812448
add a comment |
Here's a simple query to match up foreign keys to their referenced tables/columns:
SELECT
o1.name AS FK_table,
c1.name AS FK_column,
fk.name AS FK_name,
o2.name AS PK_table,
c2.name AS PK_column,
pk.name AS PK_name,
fk.delete_referential_action_desc AS Delete_Action,
fk.update_referential_action_desc AS Update_Action
FROM sys.objects o1
INNER JOIN sys.foreign_keys fk
ON o1.object_id = fk.parent_object_id
INNER JOIN sys.foreign_key_columns fkc
ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.columns c1
ON fkc.parent_object_id = c1.object_id
AND fkc.parent_column_id = c1.column_id
INNER JOIN sys.columns c2
ON fkc.referenced_object_id = c2.object_id
AND fkc.referenced_column_id = c2.column_id
INNER JOIN sys.objects o2
ON fk.referenced_object_id = o2.object_id
INNER JOIN sys.key_constraints pk
ON fk.referenced_object_id = pk.parent_object_id
AND fk.key_index_id = pk.unique_index_id
ORDER BY o1.name, o2.name, fkc.constraint_column_id
The output has eight columns: the table and column names for the foreign keys (FK_table, FK_column), the names of the foreign-key constraints (FK_name), the referenced PK or unique index table and column names (PK_table, PK_column), the name of the referenced PK or unique index (PK_name), and the update/delete cascade actions (Delete_Action, Update_Action).
(Edited to add some more output columns.)
EDIT: I'm back 6 years later with an improved version of this. I realized that the original query doesn't really handle multi-column foreign keys well, and I also wanted to be able to quickly identify disabled, untrusted, or unindexed foreign keys. So here's the new version that corrects all of that.
Multi-column keys are shown as comma-separated lists in FK_columns and PK_columns, using the traditional FOR XML/STUFF abuse. The FK_indexes column shows the names of any indexes on the foreign-key table that could potentially be used to satisfy seeks using the foreign-key columns (mainly for optimizing deletes or updates to the primary key table). If it's NULL, then you've got an unindexed foreign key. You can tweak the ORDER BY, or add a WHERE clause (commented out below) if you want to sort by the PK table name, filter for specific PK/FK tables, etc.
SELECT
fk.is_disabled,
fk.is_not_trusted,
OBJECT_SCHEMA_NAME(o1.object_id) AS FK_schema,
o1.name AS FK_table,
--Generate list of columns in referring side of foreign key
STUFF(
(
SELECT ', ' + c1.name AS [text()]
FROM sys.columns c1 INNER
JOIN sys.foreign_key_columns fkc
ON c1.object_id = fkc.parent_object_id
AND c1.column_id = fkc.parent_column_id
WHERE fkc.constraint_object_id = fk.object_id
FOR XML PATH('')
), 1, 2, '') AS FK_columns,
--Look for any indexes that will fully satisfy the foreign key columns
STUFF(
(
SELECT ', ' + i.name AS [text()]
FROM sys.indexes i
WHERE i.object_id = o1.object_id
AND NOT EXISTS ( --Find foreign key columns that don't match the index key columns
SELECT fkc.constraint_column_id, fkc.parent_column_id
FROM sys.foreign_key_columns fkc
WHERE fkc.constraint_object_id = fk.object_id
EXCEPT
SELECT ic.key_ordinal, ic.column_id
FROM sys.index_columns ic
WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id
)
FOR XML PATH('')
), 1, 2, '') AS FK_indexes,
fk.name AS FK_name,
OBJECT_SCHEMA_NAME(o2.object_id) AS PK_schema,
o2.name AS PK_table,
--Generate list of columns in referenced (i.e. PK) side of foreign key
STUFF(
(
SELECT ', ' + c2.name AS [text()]
FROM sys.columns c2
INNER JOIN sys.foreign_key_columns fkc
ON c2.object_id = fkc.referenced_object_id
AND c2.column_id = fkc.referenced_column_id
WHERE fkc.constraint_object_id = fk.object_id
FOR XML PATH('')
), 1, 2, '') AS PK_columns,
pk.name AS PK_name,
fk.delete_referential_action_desc AS Delete_Action,
fk.update_referential_action_desc AS Update_Action
FROM sys.objects o1
INNER JOIN sys.foreign_keys fk
ON o1.object_id = fk.parent_object_id
INNER JOIN sys.objects o2
ON fk.referenced_object_id = o2.object_id
INNER JOIN sys.key_constraints pk
ON fk.referenced_object_id = pk.parent_object_id
AND fk.key_index_id = pk.unique_index_id
--WHERE o2.name = 'Company_Address'
ORDER BY o1.name, o2.name
answered Jan 10 '13 at 19:06
db2db2
8,01812448
add a comment |
Here's a simple query to match up foreign keys to their referenced tables/columns:
SELECT
o1.name AS FK_table,
c1.name AS FK_column,
fk.name AS FK_name,
o2.name AS PK_table,
c2.name AS PK_column,
pk.name AS PK_name,
fk.delete_referential_action_desc AS Delete_Action,
fk.update_referential_action_desc AS Update_Action
FROM sys.objects o1
INNER JOIN sys.foreign_keys fk
ON o1.object_id = fk.parent_object_id
INNER JOIN sys.foreign_key_columns fkc
ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.columns c1
ON fkc.parent_object_id = c1.object_id
AND fkc.parent_column_id = c1.column_id
INNER JOIN sys.columns c2
ON fkc.referenced_object_id = c2.object_id
AND fkc.referenced_column_id = c2.column_id
INNER JOIN sys.objects o2
ON fk.referenced_object_id = o2.object_id
INNER JOIN sys.key_constraints pk
ON fk.referenced_object_id = pk.parent_object_id
AND fk.key_index_id = pk.unique_index_id
ORDER BY o1.name, o2.name, fkc.constraint_column_id
The output has eight columns: the table and column names for the foreign keys (FK_table, FK_column), the names of the foreign-key constraints (FK_name), the referenced PK or unique index table and column names (PK_table, PK_column), the name of the referenced PK or unique index (PK_name), and the update/delete cascade actions (Delete_Action, Update_Action).
(Edited to add some more output columns.)
EDIT: I'm back 6 years later with an improved version of this. I realized that the original query doesn't really handle multi-column foreign keys well, and I also wanted to be able to quickly identify disabled, untrusted, or unindexed foreign keys. So here's the new version that corrects all of that.
Multi-column keys are shown as comma-separated lists in FK_columns and PK_columns, using the traditional FOR XML/STUFF abuse. The FK_indexes column shows the names of any indexes on the foreign-key table that could potentially be used to satisfy seeks using the foreign-key columns (mainly for optimizing deletes or updates to the primary key table). If it's NULL, then you've got an unindexed foreign key. You can tweak the ORDER BY, or add a WHERE clause (commented out below) if you want to sort by the PK table name, filter for specific PK/FK tables, etc.
SELECT
fk.is_disabled,
fk.is_not_trusted,
OBJECT_SCHEMA_NAME(o1.object_id) AS FK_schema,
o1.name AS FK_table,
--Generate list of columns in referring side of foreign key
STUFF(
(
SELECT ', ' + c1.name AS [text()]
FROM sys.columns c1 INNER
JOIN sys.foreign_key_columns fkc
ON c1.object_id = fkc.parent_object_id
AND c1.column_id = fkc.parent_column_id
WHERE fkc.constraint_object_id = fk.object_id
FOR XML PATH('')
), 1, 2, '') AS FK_columns,
--Look for any indexes that will fully satisfy the foreign key columns
STUFF(
(
SELECT ', ' + i.name AS [text()]
FROM sys.indexes i
WHERE i.object_id = o1.object_id
AND NOT EXISTS ( --Find foreign key columns that don't match the index key columns
SELECT fkc.constraint_column_id, fkc.parent_column_id
FROM sys.foreign_key_columns fkc
WHERE fkc.constraint_object_id = fk.object_id
EXCEPT
SELECT ic.key_ordinal, ic.column_id
FROM sys.index_columns ic
WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id
)
FOR XML PATH('')
), 1, 2, '') AS FK_indexes,
fk.name AS FK_name,
OBJECT_SCHEMA_NAME(o2.object_id) AS PK_schema,
o2.name AS PK_table,
--Generate list of columns in referenced (i.e. PK) side of foreign key
STUFF(
(
SELECT ', ' + c2.name AS [text()]
FROM sys.columns c2
INNER JOIN sys.foreign_key_columns fkc
ON c2.object_id = fkc.referenced_object_id
AND c2.column_id = fkc.referenced_column_id
WHERE fkc.constraint_object_id = fk.object_id
FOR XML PATH('')
), 1, 2, '') AS PK_columns,
pk.name AS PK_name,
fk.delete_referential_action_desc AS Delete_Action,
fk.update_referential_action_desc AS Update_Action
FROM sys.objects o1
INNER JOIN sys.foreign_keys fk
ON o1.object_id = fk.parent_object_id
INNER JOIN sys.objects o2
ON fk.referenced_object_id = o2.object_id
INNER JOIN sys.key_constraints pk
ON fk.referenced_object_id = pk.parent_object_id
AND fk.key_index_id = pk.unique_index_id
--WHERE o2.name = 'Company_Address'
ORDER BY o1.name, o2.name
answered Jan 10 '13 at 19:06
db2db2
8,01812448
Here's a simple query to match up foreign keys to their referenced tables/columns:
SELECT
o1.name AS FK_table,
c1.name AS FK_column,
fk.name AS FK_name,
o2.name AS PK_table,
c2.name AS PK_column,
pk.name AS PK_name,
fk.delete_referential_action_desc AS Delete_Action,
fk.update_referential_action_desc AS Update_Action
FROM sys.objects o1
INNER JOIN sys.foreign_keys fk
ON o1.object_id = fk.parent_object_id
INNER JOIN sys.foreign_key_columns fkc
ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.columns c1
ON fkc.parent_object_id = c1.object_id
AND fkc.parent_column_id = c1.column_id
INNER JOIN sys.columns c2
ON fkc.referenced_object_id = c2.object_id
AND fkc.referenced_column_id = c2.column_id
INNER JOIN sys.objects o2
ON fk.referenced_object_id = o2.object_id
INNER JOIN sys.key_constraints pk
ON fk.referenced_object_id = pk.parent_object_id
AND fk.key_index_id = pk.unique_index_id
ORDER BY o1.name, o2.name, fkc.constraint_column_id
The output has eight columns: the table and column names for the foreign keys (FK_table, FK_column), the names of the foreign-key constraints (FK_name), the referenced PK or unique index table and column names (PK_table, PK_column), the name of the referenced PK or unique index (PK_name), and the update/delete cascade actions (Delete_Action, Update_Action).
(Edited to add some more output columns.)
EDIT: I'm back 6 years later with an improved version of this. I realized that the original query doesn't really handle multi-column foreign keys well, and I also wanted to be able to quickly identify disabled, untrusted, or unindexed foreign keys. So here's the new version that corrects all of that.
Multi-column keys are shown as comma-separated lists in FK_columns and PK_columns, using the traditional FOR XML/STUFF abuse. The FK_indexes column shows the names of any indexes on the foreign-key table that could potentially be used to satisfy seeks using the foreign-key columns (mainly for optimizing deletes or updates to the primary key table). If it's NULL, then you've got an unindexed foreign key. You can tweak the ORDER BY, or add a WHERE clause (commented out below) if you want to sort by the PK table name, filter for specific PK/FK tables, etc.
SELECT
fk.is_disabled,
fk.is_not_trusted,
OBJECT_SCHEMA_NAME(o1.object_id) AS FK_schema,
o1.name AS FK_table,
--Generate list of columns in referring side of foreign key
STUFF(
(
SELECT ', ' + c1.name AS [text()]
FROM sys.columns c1 INNER
JOIN sys.foreign_key_columns fkc
ON c1.object_id = fkc.parent_object_id
AND c1.column_id = fkc.parent_column_id
WHERE fkc.constraint_object_id = fk.object_id
FOR XML PATH('')
), 1, 2, '') AS FK_columns,
--Look for any indexes that will fully satisfy the foreign key columns
STUFF(
(
SELECT ', ' + i.name AS [text()]
FROM sys.indexes i
WHERE i.object_id = o1.object_id
AND NOT EXISTS ( --Find foreign key columns that don't match the index key columns
SELECT fkc.constraint_column_id, fkc.parent_column_id
FROM sys.foreign_key_columns fkc
WHERE fkc.constraint_object_id = fk.object_id
EXCEPT
SELECT ic.key_ordinal, ic.column_id
FROM sys.index_columns ic
WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id
)
FOR XML PATH('')
), 1, 2, '') AS FK_indexes,
fk.name AS FK_name,
OBJECT_SCHEMA_NAME(o2.object_id) AS PK_schema,
o2.name AS PK_table,
--Generate list of columns in referenced (i.e. PK) side of foreign key
STUFF(
(
SELECT ', ' + c2.name AS [text()]
FROM sys.columns c2
INNER JOIN sys.foreign_key_columns fkc
ON c2.object_id = fkc.referenced_object_id
AND c2.column_id = fkc.referenced_column_id
WHERE fkc.constraint_object_id = fk.object_id
FOR XML PATH('')
), 1, 2, '') AS PK_columns,
pk.name AS PK_name,
fk.delete_referential_action_desc AS Delete_Action,
fk.update_referential_action_desc AS Update_Action
FROM sys.objects o1
INNER JOIN sys.foreign_keys fk
ON o1.object_id = fk.parent_object_id
INNER JOIN sys.objects o2
ON fk.referenced_object_id = o2.object_id
INNER JOIN sys.key_constraints pk
ON fk.referenced_object_id = pk.parent_object_id
AND fk.key_index_id = pk.unique_index_id
--WHERE o2.name = 'Company_Address'
ORDER BY o1.name, o2.name
answered Jan 10 '13 at 19:06
db2db2
8,01812448
edited 7 mins ago
answered Jan 10 '13 at 19:06
db2db2
8,01812448
answered Jan 10 '13 at 19:06
db2db2
8,01812448
answered Jan 10 '13 at 19:06
db2db2
8,01812448
8,01812448
add a comment |
add a comment |
This query nets you all of the FK relationships in the database - FK constraint name, schema/table of referencing table, referencing column name, schema/table of referenced table, and referenced column name. There will be multiple rows for a multi-column constraint.
SELECT
FK = OBJECT_NAME(pt.constraint_object_id),
Referencing_table = QUOTENAME(OBJECT_SCHEMA_NAME(pt.parent_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(pt.parent_object_id)),
Referencing_col = QUOTENAME(pc.name),
Referenced_table = QUOTENAME(OBJECT_SCHEMA_NAME(pt.referenced_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(pt.referenced_object_id)),
Referenced_col = QUOTENAME(rc.name)
FROM sys.foreign_key_columns AS pt
INNER JOIN sys.columns AS pc
ON pt.parent_object_id = pc.[object_id]
AND pt.parent_column_id = pc.column_id
INNER JOIN sys.columns AS rc
ON pt.referenced_column_id = rc.column_id
AND pt.referenced_object_id = rc.[object_id]
ORDER BY Referencing_table, FK, pt.constraint_column_id;
If you are after the columns from a specific primary key constraint, and you already know the name of that PK constraint, you can write this:
DECLARE @PK_Constraint SYSNAME = N'Name of PK constraint';
SELECT
FK = OBJECT_NAME(fkc.constraint_object_id),
Referencing_table = QUOTENAME(OBJECT_SCHEMA_NAME(fkc.parent_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(fkc.parent_object_id)),
Referencing_col = QUOTENAME(pc.name),
Referenced_table = QUOTENAME(OBJECT_SCHEMA_NAME(fkc.referenced_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(fkc.referenced_object_id)),
Referenced_col = QUOTENAME(rc.name)
FROM sys.foreign_key_columns AS fkc
INNER JOIN sys.columns AS pc
ON fkc.parent_object_id = pc.[object_id]
AND fkc.parent_column_id = pc.column_id
INNER JOIN sys.columns AS rc
ON fkc.referenced_column_id = rc.column_id
AND fkc.referenced_object_id = rc.[object_id]
WHERE EXISTS
(
SELECT 1 FROM sys.indexes AS i
INNER JOIN sys.foreign_keys AS fk
ON i.[object_id] = fk.referenced_object_id
AND i.index_id = fk.key_index_id
AND fk.[object_id] = fkc.constraint_object_id
AND i.name = @PK_Constraint
)
ORDER BY Referencing_table, FK, fkc.constraint_column_id;
If you just want to include the PK name along with the other information:
SELECT
FK = OBJECT_NAME(fkc.constraint_object_id),
Referencing_table = QUOTENAME(OBJECT_SCHEMA_NAME(fkc.parent_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(fkc.parent_object_id)),
Referencing_col = QUOTENAME(pc.name),
Referenced_table = QUOTENAME(OBJECT_SCHEMA_NAME(fkc.referenced_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(fkc.referenced_object_id)),
Referenced_col = QUOTENAME(rc.name),
PK = pk.name
FROM sys.foreign_key_columns AS fkc
INNER JOIN sys.columns AS pc
ON fkc.parent_object_id = pc.[object_id]
AND fkc.parent_column_id = pc.column_id
INNER JOIN sys.columns AS rc
ON fkc.referenced_column_id = rc.column_id
AND fkc.referenced_object_id = rc.[object_id]
INNER JOIN (SELECT i.name, fk.[object_id]
FROM sys.indexes AS i
INNER JOIN sys.foreign_keys AS fk
ON i.[object_id] = fk.referenced_object_id
AND i.index_id = fk.key_index_id
) AS pk
ON pk.[object_id] = fkc.constraint_object_id
ORDER BY Referencing_table, FK, fkc.constraint_column_id;
There are also tricks to getting the column list in, say, a comma-separated list or individual columns, instead of being spread across rows, but I'm not going to invest in modifying these queries to produce that until I know exactly which form you're after.
answered Jan 10 '13 at 19:13
Aaron Bertrand♦Aaron Bertrand
150k18284482
add a comment |
This query nets you all of the FK relationships in the database - FK constraint name, schema/table of referencing table, referencing column name, schema/table of referenced table, and referenced column name. There will be multiple rows for a multi-column constraint.
SELECT
FK = OBJECT_NAME(pt.constraint_object_id),
Referencing_table = QUOTENAME(OBJECT_SCHEMA_NAME(pt.parent_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(pt.parent_object_id)),
Referencing_col = QUOTENAME(pc.name),
Referenced_table = QUOTENAME(OBJECT_SCHEMA_NAME(pt.referenced_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(pt.referenced_object_id)),
Referenced_col = QUOTENAME(rc.name)
FROM sys.foreign_key_columns AS pt
INNER JOIN sys.columns AS pc
ON pt.parent_object_id = pc.[object_id]
AND pt.parent_column_id = pc.column_id
INNER JOIN sys.columns AS rc
ON pt.referenced_column_id = rc.column_id
AND pt.referenced_object_id = rc.[object_id]
ORDER BY Referencing_table, FK, pt.constraint_column_id;
If you are after the columns from a specific primary key constraint, and you already know the name of that PK constraint, you can write this:
DECLARE @PK_Constraint SYSNAME = N'Name of PK constraint';
SELECT
FK = OBJECT_NAME(fkc.constraint_object_id),
Referencing_table = QUOTENAME(OBJECT_SCHEMA_NAME(fkc.parent_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(fkc.parent_object_id)),
Referencing_col = QUOTENAME(pc.name),
Referenced_table = QUOTENAME(OBJECT_SCHEMA_NAME(fkc.referenced_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(fkc.referenced_object_id)),
Referenced_col = QUOTENAME(rc.name)
FROM sys.foreign_key_columns AS fkc
INNER JOIN sys.columns AS pc
ON fkc.parent_object_id = pc.[object_id]
AND fkc.parent_column_id = pc.column_id
INNER JOIN sys.columns AS rc
ON fkc.referenced_column_id = rc.column_id
AND fkc.referenced_object_id = rc.[object_id]
WHERE EXISTS
(
SELECT 1 FROM sys.indexes AS i
INNER JOIN sys.foreign_keys AS fk
ON i.[object_id] = fk.referenced_object_id
AND i.index_id = fk.key_index_id
AND fk.[object_id] = fkc.constraint_object_id
AND i.name = @PK_Constraint
)
ORDER BY Referencing_table, FK, fkc.constraint_column_id;
If you just want to include the PK name along with the other information:
SELECT
FK = OBJECT_NAME(fkc.constraint_object_id),
Referencing_table = QUOTENAME(OBJECT_SCHEMA_NAME(fkc.parent_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(fkc.parent_object_id)),
Referencing_col = QUOTENAME(pc.name),
Referenced_table = QUOTENAME(OBJECT_SCHEMA_NAME(fkc.referenced_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(fkc.referenced_object_id)),
Referenced_col = QUOTENAME(rc.name),
PK = pk.name
FROM sys.foreign_key_columns AS fkc
INNER JOIN sys.columns AS pc
ON fkc.parent_object_id = pc.[object_id]
AND fkc.parent_column_id = pc.column_id
INNER JOIN sys.columns AS rc
ON fkc.referenced_column_id = rc.column_id
AND fkc.referenced_object_id = rc.[object_id]
INNER JOIN (SELECT i.name, fk.[object_id]
FROM sys.indexes AS i
INNER JOIN sys.foreign_keys AS fk
ON i.[object_id] = fk.referenced_object_id
AND i.index_id = fk.key_index_id
) AS pk
ON pk.[object_id] = fkc.constraint_object_id
ORDER BY Referencing_table, FK, fkc.constraint_column_id;
There are also tricks to getting the column list in, say, a comma-separated list or individual columns, instead of being spread across rows, but I'm not going to invest in modifying these queries to produce that until I know exactly which form you're after.
answered Jan 10 '13 at 19:13
Aaron Bertrand♦Aaron Bertrand
150k18284482
add a comment |
This query nets you all of the FK relationships in the database - FK constraint name, schema/table of referencing table, referencing column name, schema/table of referenced table, and referenced column name. There will be multiple rows for a multi-column constraint.
SELECT
FK = OBJECT_NAME(pt.constraint_object_id),
Referencing_table = QUOTENAME(OBJECT_SCHEMA_NAME(pt.parent_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(pt.parent_object_id)),
Referencing_col = QUOTENAME(pc.name),
Referenced_table = QUOTENAME(OBJECT_SCHEMA_NAME(pt.referenced_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(pt.referenced_object_id)),
Referenced_col = QUOTENAME(rc.name)
FROM sys.foreign_key_columns AS pt
INNER JOIN sys.columns AS pc
ON pt.parent_object_id = pc.[object_id]
AND pt.parent_column_id = pc.column_id
INNER JOIN sys.columns AS rc
ON pt.referenced_column_id = rc.column_id
AND pt.referenced_object_id = rc.[object_id]
ORDER BY Referencing_table, FK, pt.constraint_column_id;
If you are after the columns from a specific primary key constraint, and you already know the name of that PK constraint, you can write this:
DECLARE @PK_Constraint SYSNAME = N'Name of PK constraint';
SELECT
FK = OBJECT_NAME(fkc.constraint_object_id),
Referencing_table = QUOTENAME(OBJECT_SCHEMA_NAME(fkc.parent_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(fkc.parent_object_id)),
Referencing_col = QUOTENAME(pc.name),
Referenced_table = QUOTENAME(OBJECT_SCHEMA_NAME(fkc.referenced_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(fkc.referenced_object_id)),
Referenced_col = QUOTENAME(rc.name)
FROM sys.foreign_key_columns AS fkc
INNER JOIN sys.columns AS pc
ON fkc.parent_object_id = pc.[object_id]
AND fkc.parent_column_id = pc.column_id
INNER JOIN sys.columns AS rc
ON fkc.referenced_column_id = rc.column_id
AND fkc.referenced_object_id = rc.[object_id]
WHERE EXISTS
(
SELECT 1 FROM sys.indexes AS i
INNER JOIN sys.foreign_keys AS fk
ON i.[object_id] = fk.referenced_object_id
AND i.index_id = fk.key_index_id
AND fk.[object_id] = fkc.constraint_object_id
AND i.name = @PK_Constraint
)
ORDER BY Referencing_table, FK, fkc.constraint_column_id;
If you just want to include the PK name along with the other information:
SELECT
FK = OBJECT_NAME(fkc.constraint_object_id),
Referencing_table = QUOTENAME(OBJECT_SCHEMA_NAME(fkc.parent_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(fkc.parent_object_id)),
Referencing_col = QUOTENAME(pc.name),
Referenced_table = QUOTENAME(OBJECT_SCHEMA_NAME(fkc.referenced_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(fkc.referenced_object_id)),
Referenced_col = QUOTENAME(rc.name),
PK = pk.name
FROM sys.foreign_key_columns AS fkc
INNER JOIN sys.columns AS pc
ON fkc.parent_object_id = pc.[object_id]
AND fkc.parent_column_id = pc.column_id
INNER JOIN sys.columns AS rc
ON fkc.referenced_column_id = rc.column_id
AND fkc.referenced_object_id = rc.[object_id]
INNER JOIN (SELECT i.name, fk.[object_id]
FROM sys.indexes AS i
INNER JOIN sys.foreign_keys AS fk
ON i.[object_id] = fk.referenced_object_id
AND i.index_id = fk.key_index_id
) AS pk
ON pk.[object_id] = fkc.constraint_object_id
ORDER BY Referencing_table, FK, fkc.constraint_column_id;
There are also tricks to getting the column list in, say, a comma-separated list or individual columns, instead of being spread across rows, but I'm not going to invest in modifying these queries to produce that until I know exactly which form you're after.
answered Jan 10 '13 at 19:13
Aaron Bertrand♦Aaron Bertrand
150k18284482
This query nets you all of the FK relationships in the database - FK constraint name, schema/table of referencing table, referencing column name, schema/table of referenced table, and referenced column name. There will be multiple rows for a multi-column constraint.
SELECT
FK = OBJECT_NAME(pt.constraint_object_id),
Referencing_table = QUOTENAME(OBJECT_SCHEMA_NAME(pt.parent_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(pt.parent_object_id)),
Referencing_col = QUOTENAME(pc.name),
Referenced_table = QUOTENAME(OBJECT_SCHEMA_NAME(pt.referenced_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(pt.referenced_object_id)),
Referenced_col = QUOTENAME(rc.name)
FROM sys.foreign_key_columns AS pt
INNER JOIN sys.columns AS pc
ON pt.parent_object_id = pc.[object_id]
AND pt.parent_column_id = pc.column_id
INNER JOIN sys.columns AS rc
ON pt.referenced_column_id = rc.column_id
AND pt.referenced_object_id = rc.[object_id]
ORDER BY Referencing_table, FK, pt.constraint_column_id;
If you are after the columns from a specific primary key constraint, and you already know the name of that PK constraint, you can write this:
DECLARE @PK_Constraint SYSNAME = N'Name of PK constraint';
SELECT
FK = OBJECT_NAME(fkc.constraint_object_id),
Referencing_table = QUOTENAME(OBJECT_SCHEMA_NAME(fkc.parent_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(fkc.parent_object_id)),
Referencing_col = QUOTENAME(pc.name),
Referenced_table = QUOTENAME(OBJECT_SCHEMA_NAME(fkc.referenced_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(fkc.referenced_object_id)),
Referenced_col = QUOTENAME(rc.name)
FROM sys.foreign_key_columns AS fkc
INNER JOIN sys.columns AS pc
ON fkc.parent_object_id = pc.[object_id]
AND fkc.parent_column_id = pc.column_id
INNER JOIN sys.columns AS rc
ON fkc.referenced_column_id = rc.column_id
AND fkc.referenced_object_id = rc.[object_id]
WHERE EXISTS
(
SELECT 1 FROM sys.indexes AS i
INNER JOIN sys.foreign_keys AS fk
ON i.[object_id] = fk.referenced_object_id
AND i.index_id = fk.key_index_id
AND fk.[object_id] = fkc.constraint_object_id
AND i.name = @PK_Constraint
)
ORDER BY Referencing_table, FK, fkc.constraint_column_id;
If you just want to include the PK name along with the other information:
SELECT
FK = OBJECT_NAME(fkc.constraint_object_id),
Referencing_table = QUOTENAME(OBJECT_SCHEMA_NAME(fkc.parent_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(fkc.parent_object_id)),
Referencing_col = QUOTENAME(pc.name),
Referenced_table = QUOTENAME(OBJECT_SCHEMA_NAME(fkc.referenced_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(fkc.referenced_object_id)),
Referenced_col = QUOTENAME(rc.name),
PK = pk.name
FROM sys.foreign_key_columns AS fkc
INNER JOIN sys.columns AS pc
ON fkc.parent_object_id = pc.[object_id]
AND fkc.parent_column_id = pc.column_id
INNER JOIN sys.columns AS rc
ON fkc.referenced_column_id = rc.column_id
AND fkc.referenced_object_id = rc.[object_id]
INNER JOIN (SELECT i.name, fk.[object_id]
FROM sys.indexes AS i
INNER JOIN sys.foreign_keys AS fk
ON i.[object_id] = fk.referenced_object_id
AND i.index_id = fk.key_index_id
) AS pk
ON pk.[object_id] = fkc.constraint_object_id
ORDER BY Referencing_table, FK, fkc.constraint_column_id;
There are also tricks to getting the column list in, say, a comma-separated list or individual columns, instead of being spread across rows, but I'm not going to invest in modifying these queries to produce that until I know exactly which form you're after.
answered Jan 10 '13 at 19:13
Aaron Bertrand♦Aaron Bertrand
150k18284482
edited Jan 10 '13 at 19:40
answered Jan 10 '13 at 19:13
Aaron Bertrand♦Aaron Bertrand
150k18284482
answered Jan 10 '13 at 19:13
Aaron Bertrand♦Aaron Bertrand
150k18284482
answered Jan 10 '13 at 19:13
Aaron Bertrand♦Aaron Bertrand
150k18284482
150k18284482
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f31720%2ffind-the-foreign-keys-associated-with-a-given-primary-key%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
