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
Here is the first approach I came up with:
DECLARE @ChosenLanguage INT = 48;
SELECT sc.Id, Result = MAX(COALESCE(
CASE WHEN lst.LanguageId = @ChosenLanguage THEN st.Text END,
CASE WHEN lst.LanguageId = sk.DefaultLanguageId THEN st.Text END)
)
FROM dbo.SupportCategories AS sc
INNER JOIN dbo.StringKeys AS sk
ON sc.StringKeyId = sk.Id
LEFT OUTER JOIN dbo.LanguageStringTranslations AS lst
ON sk.Id = lst.StringKeyId
AND lst.LanguageId IN (sk.DefaultLanguageId, @ChosenLanguage)
LEFT OUTER JOIN dbo.StringTranslations AS st
ON st.Id = lst.StringTranslationId
--WHERE sc.Id = 1
GROUP BY sc.Id
ORDER BY sc.Id;
Basically, get the potential strings that match the chosen language and get all the default strings, then aggregate so you only pick one per Id
- prioritize on the chosen language, then take the default as a fallback.
You can probably do similar things with UNION
/EXCEPT
but I suspect this will almost always lead to multiple scans against the same objects.
Best Answer
This T-SQL will generate a series of
ALTER TABLE
statements that you can copy-and-paste into a query window in SSMS to remove columns that are neither primary keys nor foreign keys.It will correctly handle tables in various schemas, as well.
You may also have to first remove any default constraints: