How to check the constraints in the bank

Question

Hello, how do I check if my constraints are linked in the entire database? I need a query for Oracle and SQL Server.

Answer 1

Try this to see all constraints:

select * from information_schema.referential_constraints
where constraint_schema = 'NOME_DO_SEU_BANCO'

For SQL Server, you have more information here: https://msdn.microsoft.com/en-us/library/ms186778.aspx

And for Oracle try this by replacing the values:

select R.TABLE_NAME
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE U
inner join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS FK
    on U.CONSTRAINT_CATALOG = FK.UNIQUE_CONSTRAINT_CATALOG
    and U.CONSTRAINT_SCHEMA = FK.UNIQUE_CONSTRAINT_SCHEMA
    and U.CONSTRAINT_NAME = FK.UNIQUE_CONSTRAINT_NAME
inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE R
    ON R.CONSTRAINT_CATALOG = FK.CONSTRAINT_CATALOG
    AND R.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA
    AND R.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
WHERE U.COLUMN_NAME = 'aaaaaaaaaaaaaaaa'
  AND U.TABLE_CATALOG = 'bbbbbbbbbbbbbbbbb'
  AND U.TABLE_SCHEMA = 'cccccccccccc'
  AND U.TABLE_NAME = 'ddddddddddddd'