If you want to know which tables are all referencing a specific table using a foreign key, you can use one the following system tables:

1. sysconstraints and sysreferences
2. syskeys

The first way will only work if you have constraints backing the foreign keys. If you have just foreign keys without any referential constraints, you will not be able to see anything.

Here’s how you can use the syskeys system table to get information about foreign keys:

First the following columns of syskeys are relevant to us:

• id: it’s the ID of the table having the foreign/primary/common key.
• type: it tells you whether it’s a primary, foreign or common key. In our case we’re only interested in foreign keys, so type=2.
• keycnt: since this table contains 8 columns with column IDs, it’s useful to know how many of them actually contain something.
• depid: it is the ID of the table we want to find foreign keys for.
• key1 to key8: these are the column IDs in the referencing table.
• depkey1 to depkey8: these are the column IDs in the referenced table.

Let’s say we want to find all tables with a foreign key to a table called ‘report’. We’d then have the following WHERE clause:

select ... from	syskeys k
where k.type = 2 and k.depid = object_id('report')

Meaning: look for foreign keys (type=2) referecing the report table(depid=).

Now we want to get the name of the referencing table:

select object_name(k.id)

The names of the referencing columns:

select	object_name(k.id),
		col_name(k.depid, depkey1)
		+', '+col_name(k.depid, depkey2)
		+', '+col_name(k.depid, depkey3)
		+', '+col_name(k.depid, depkey4)
		+', '+col_name(k.depid, depkey5)
		+', '+col_name(k.depid, depkey6)
		+', '+col_name(k.depid, depkey7)
		+', '+col_name(k.depid, depkey8)
from	syskeys k
where	k.type = 2
and		k.depid = object_id('report')

You’ll notice the output looks ugly because:

• object_name returns a huge string with many trailing blanks
• the list of columns looks like key, , , , , , ,

In order to make it look nicer, we’ll need to trim the strings for trailing spaces, limit the length of the individual strings to 30 characters and remove the extra commas.

For the extra commas, we can use syskeys.keycnt which will tell us how many depkeyX columns are filled. And we can use the fact that substring(‘blabla’, X, Y) will return NULL if X is less than 1. This means we need a function which returns 1 if the column index is less than keycnt and returns 0 or a negative number otherwise. Luckily such a function does exist: sign. It will return -1 if the argument is negative, 0 if it’s 0 and 1 if it’s positive. So we can use sign(keycnt – columnIndex + 1). If there are 3 columns:

• sign(3 – 1 +1) = 1
• sign(3 – 2 +1) = 0
• sign(3 – 3 +1) = -1
• sign(3 – 4 +1) = -1
• sign(3 – 5 +1) = -1
• sign(3 – 6 +1) = -1
• sign(3 – 7 +1) = -1
• sign(3 – 8 +1) = -1

So we end up with something like this:

select	rtrim(substring(object_name(k.id), 1, 30)),
		rtrim(substring(col_name(k.depid, depkey1),sign(keycnt),30))
		+rtrim(substring(', '+col_name(k.depid, depkey2),sign(keycnt-1),30))
		+rtrim(substring(', '+col_name(k.depid, depkey3),sign(keycnt-2),30))
		+rtrim(substring(', '+col_name(k.depid, depkey4),sign(keycnt-3),30))
		+rtrim(substring(', '+col_name(k.depid, depkey5),sign(keycnt-4),30))
		+rtrim(substring(', '+col_name(k.depid, depkey6),sign(keycnt-5),30))
		+rtrim(substring(', '+col_name(k.depid, depkey7),sign(keycnt-6),30))
		+rtrim(substring(', '+col_name(k.depid, depkey8),sign(keycnt-7),30))
		from	syskeys k
where	k.type = 2
and		k.depid = object_id('report')

And if we also want to have the column names of the referenced table:

select	rtrim(substring(object_name(k.id), 1, 30)),
		rtrim(substring(col_name(k.depid, depkey1),sign(keycnt),30))
		+rtrim(substring(', '+col_name(k.depid, depkey2),sign(keycnt-1),30))
		+rtrim(substring(', '+col_name(k.depid, depkey3),sign(keycnt-2),30))
		+rtrim(substring(', '+col_name(k.depid, depkey4),sign(keycnt-3),30))
		+rtrim(substring(', '+col_name(k.depid, depkey5),sign(keycnt-4),30))
		+rtrim(substring(', '+col_name(k.depid, depkey6),sign(keycnt-5),30))
		+rtrim(substring(', '+col_name(k.depid, depkey7),sign(keycnt-6),30))
		+rtrim(substring(', '+col_name(k.depid, depkey8),sign(keycnt-7),30)),
		rtrim(substring(col_name(k.id, key1),sign(keycnt),30))
		+rtrim(substring(', '+col_name(k.id, key2),sign(keycnt-1),30))
		+rtrim(substring(', '+col_name(k.id, key3),sign(keycnt-2),30))
		+rtrim(substring(', '+col_name(k.id, key4),sign(keycnt-3),30))
		+rtrim(substring(', '+col_name(k.id, key5),sign(keycnt-4),30))
		+rtrim(substring(', '+col_name(k.id, key6),sign(keycnt-5),30))
		+rtrim(substring(', '+col_name(k.id, key7),sign(keycnt-6),30))
		+rtrim(substring(', '+col_name(k.id, key8),sign(keycnt-7),30))
from	syskeys k
where	k.type = 2
and		k.depid = object_id('report')