0

I am trying to view all foreign keys of my database and I have the following code to do that:

SELECT 
  tc.TABLE_NAME,
  sc.COLUMN_NAME,
  CONSTRAINT_NAME,
  CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc
inner join INFORMATION_SCHEMA.COLUMNS as sc
on sc.TABLE_NAME = tc.TABLE_NAME
order by tc.TABLE_NAME

But it seems to give more results than it should be. It gives around 30.000 result and it should be no more than 2000. What I am doing wrong. PS: I need this to return the table name, column name, column id and constraint type for further implementation in Laravel application.

Dale K
  • 25,246
  • 15
  • 42
  • 71
nycolaescu
  • 1
  • 4
  • 1
    check if this might might help https://stackoverflow.com/questions/483193/how-can-i-list-all-foreign-keys-referencing-a-given-table-in-sql-server – Sreedhar Danturthi Jun 01 '20 at 09:03

2 Answers2

0

Try to add "where CONSTRAINT_TYPE = 'FOREIGN KEY'":

SELECT 
tc.TABLE_NAME,
sc.COLUMN_NAME,
CONSTRAINT_NAME,
CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc
inner join INFORMATION_SCHEMA.COLUMNS as sc
on sc.TABLE_NAME = tc.TABLE_NAME
where CONSTRAINT_TYPE = 'FOREIGN KEY'
order by tc.TABLE_NAME;
ikh
  • 141
  • 1
  • 4
  • That's only one of the problems with the original query. Because all columns of the table are joined, not only the FK columns. Table sys.foreign_key_columns should be used. – Wouter Jun 01 '20 at 09:17
0

I think the best option here is to use EXEC sp_fkeys 'TableName'. Check this out for more details

check this question

SaboSuke
  • 634
  • 6
  • 21