How to see contents of Check Constraint

Is there a TSQL script that will all开发者_JAVA百科ow me to see the contents of a constraint. I found a question regarding Oracle but I need a TSQL script.

How to see contents of Check Constraint on Oracle

I am aware of sys.check_constraints, however, the 'definition' comes back null for all objects.

Select * from sys.check_constraints

Another way

for check constraints

select definition,name
 from sys.check_constraints

for default constraints

select definition,name
 from sys.default_constraints

and yet another way

 SELECT object_definition(OBJECT_ID(CONSTRAINT_NAME)),* 
 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
 where CONSTRAINT_TYPE <> 'PRIMARY KEY'

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

sys.default_constraints for default constraints on columns

sys.check_constraints for check constraints on columns

sys.key_constraints for key constraints (e.g. primary keys)

sys.foreign_keys for foreign key relations

sys.check_constraints for check constraints on columns, not valid for check constraints on tables. Eg: CONSTRAINT CK_NumeroUsadas_NumeroTotal CHECK (NumeroUsadas <= NumeroTotal AND NumeroTotal >= 0),

Search for text inside a constraint:

1.) SELECT CONSTRAINT_NAME,CHECK_CLAUSE FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS WHERE CHECK_CLAUSE like '%NumeroTotal%' or CHECK_CLAUSE LIKE '%NumeroUsadas%'

2.) SELECT object_definition(OBJECT_ID(CONSTRAINT_NAME)),* FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'CHECK' AND object_definition(OBJECT_ID(CONSTRAINT_NAME)) like '%NumeroTotal%' or object_definition(OBJECT_ID(CONSTRAINT_NAME)) LIKE '%NumeroUsadas%'

To have any check constraints, you're going to need objects of this type.

select *
from sys.objects
where sys.objects.type = 'C'

check_constraints