Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Finding which table a constraint belongs to

Tags:

I need to find out which table (name) a particular constraint belongs to.

Does anyone have any TSQL to achieve this?

like image 720
Mick Walker Avatar asked May 12 '10 07:05

Mick Walker


People also ask

How do you know which constraint a table belongs to?

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.

How do I find constraints on a table in SQL?

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.

How do I view constraints on a table in SQL Server?

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.


2 Answers

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 
like image 90
gbn Avatar answered Sep 23 '22 14:09

gbn


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.

like image 32
KM. Avatar answered Sep 24 '22 14:09

KM.