I need to find out which table (name) a particular constraint belongs to.
Does anyone have any TSQL to achieve this?
select table_name from user_constraints where (r_constraint_name) in ( select constraint_name from user_constraints where table_name = 'T' and constraint_type in ( 'P', 'U' ) ); So, we can easily find all the constraints on the table in oracle using data dictionary views.
select COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_COLUMN_NAME, REFERENCED_TABLE_NAME from information_schema. KEY_COLUMN_USAGE where TABLE_NAME = 'yourTableName'; To display all constraints on a table, implement the above syntax.
In the Object Explorer, right-click the table containing the check constraint and select Design. On the Table Designer menu, click Check Constraints.... In the Check Constraints dialog box, under Selected Check Constraint, select the constraint you wish to edit.
This will not find indexes which are in sys.indexes
SELECT OBJECT_NAME(o.parent_object_id) FROM sys.objects o WHERE o.name = 'MyConstraintName' AND o.parent_object_id <> 0
many things could be considered to be a constraint:
primary key
foreign key
unique index
check constraint
column default
your question is a little vague. Do you know the name of the constraint, the type, etc.?
Based on the limited info in your question. I suggest that you look at the source code to the master.sys.sp_helpconstraint stored procedure.
In Sql Server Management Studio, using the Object Explorer, ust navigate to: "Databases" - "System Databases" - "master" - "Programmability" - "Stored Procedures" - "System Stored Procedures" - "sys.sp_helpconstraint". It contains all the tsql to query all the various kinds of constraints.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With