Blog Post

Too many Foreign Keys

,

I’ve seen tables that are referenced by many other tables in the databases and that number was usually around some couple of foreign keys and up to 20, 30 and similar.

Working on a bug, I met an odd table with too many foreign keys. The table is referenced by 327 other tables. Here is a print screen of the query that was run and extracted the results.

So You, please run the next query and please drop me a screen shot on igormicev@gmail.com if you come up with something extraordinary like this.

SELECT
  obj.name AS [FK NAME],
  sch.name AS [schema name],
  t1.name AS [referencing table],
  c1.name AS [referencing column],
  t2.name AS [referenced table],
  c2.name AS [referenced column]
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
  ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables t1
  ON t1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
  ON t1.schema_id = sch.schema_id
INNER JOIN sys.columns c1
  ON c1.column_id = parent_column_id
  AND c1.object_id = t1.object_id
INNER JOIN sys.tables t2
  ON t2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns c2
  ON c2.column_id = referenced_column_id
  AND c2.object_id = t2.object_id
WHERE t2.name = 'you table name'
AND SCHEMA_NAME(t2.schema_id) = 'table schema'

 

Original post (opens in new tab)
View comments in original post (opens in new tab)

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating