CODEWITHSUNDEEP
sql-servert-sqlconstraints
Mick Walker
Mick Walker

Reputation: 3847

Finding which table a constraint belongs to

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

Does anyone have any TSQL to achieve this?

Upvotes: 27

Views: 33792

Answers (4)

Romil Kumar Jain
Romil Kumar Jain

Reputation: 20745

SELECT NAME AS ObjectName
    ,schema_name(o.schema_id) AS SchemaName, OBJECT_NAME(o.parent_object_id) as TableName
    ,type
    ,o.type_desc
FROM sys.objects o
WHERE o.is_ms_shipped = 0
    AND o.NAME LIKE '%ConstraintUniqueID%'
ORDER BY o.NAME

Upvotes: 0

Paul Gorbas
Paul Gorbas

Reputation: 1792

gbn, your solution dosn't seam to work?

SELECT
   OBJECT_NAME(o.parent_object_id)
FROM
   sys.objects o
WHERE
   o.name = 'MyConstraintName' AND o.parent_object_id <> 0

So if 'MyConstraintName' becomes 'FK_myConstraint' then the query becomes

SELECT OBJECT_NAME(o.parent_object_id)
FROM sys.objects o
WHERE o.name = 'MyConstraintName' 
  AND o.parent_object_id <> 0

Which produces no results

(No column name)

It seams that your 'solution' must be based on a whole lot of unstated assumptions about the schema.

Upvotes: 2

gbn
gbn

Reputation: 432200

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

Upvotes: 45

KM.
KM.

Reputation: 103579

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.

Upvotes: 2

Related Questions