How to check the constraints on the bank

0

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

    
asked by anonymous 14.07.2015 / 20:36

1 answer

0

Try this to see all the constraints:

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

For SQL Server, you have more information here: link

And for Oracle try this, overriding 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'
    
14.07.2015 / 20:56