Query returning unwanted result

Asked

Viewed 49 times

0

In my sql statement: 

SELECT DISTINCT C.TABLE_NAME,C.CONSTRAINT_NAME,C.COLUMN_NAME 
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS T 
ON T.TABLE_NAME = C.TABLE_NAME
WHERE C.TABLE_NAME = 'VALOR_CAMPO_DATA' 
AND C.COLUMN_NAME IN ('ID','REV') 
AND T.CONSTRAINT_TYPE = 'FOREIGN KEY';

I would like you to return only FK’s, but PK’s are also coming. I specified in where T.CONSTRAINT_TYPE = 'FOREIGN KEY', but it’s not working, if I run only the INFORMATION_SCHEMA.TABLE_CONSTRAINTS works,

Does anyone know how I solve this problem?

sql sql-server

  • See if this link helps

  • If you want FK’s why not make one select in sys.foreign_keysor still use proc EXEC sp_fkeys 'nome-da-tabela'?

  • 1

    The following was missing from the Inner Join: AND T.CONSTRAINT_NAME = C.CONSTRAINT_NAME Otherwise it will duplicate information because TABLE_NAME is for both PK and FK, so it brings both

  • @Andréluan publish his answer there that worked...

1 answer

4


Failed to add the following in the Inner Join:

AND T.CONSTRAINT_NAME = C.CONSTRAINT_NAME

Otherwise it will duplicate information because TABLE_NAME is both for PK and for FK, so it brings both

  • Show, thank you very much.

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.