In MSSQL, at some point, you might need to find the foreign keys of a table.
Below mydb and mytable are placeholders.
You can test this with a table having foreign key but I ‘d like to approach the query as if there is no foreign key in the table.
If the first query results with no column name as header and 9 digit numeric number, that confirms the database and table exists,
and if the rest queries are giving empty results, then there is no foreign key.
USE mydb
SELECT object_id('mytable')
SELECT
object_name(parent_object_id) ParentTableName,
object_name(referenced_object_id) RefTableName,
name
FROM sys.foreign_keys
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), 0),
UPDATE_RULE = convert(smallint,
case f.update_referential_action
when 1 then 0
when 0 then 1
else f.update_referential_action
end),
DELETE_RULE = convert(smallint,
case f.delete_referential_action
when 1 then 0
when 0 then 1
else f.delete_referential_action
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.objects o1,
sys.objects o2,
sys.columns c1,
sys.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
(o1.object_id = object_id('dbo.mytable')) 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
order by 5, 6, 7, 9, 8