CODEWITHSUNDEEP
sql-servert-sql
ramesh ummadisetty
ramesh ummadisetty

Reputation: 41

How to get column-wise constraints in SQL Server

I am using below query to get constraints on required table:

SELECT 
    OBJECT_NAME(o.object_id) AS ConstraintName, 
    SCHEMA_NAME(schema_id) AS SchemaName,
    OBJECT_NAME(parent_object_id) AS TableName,
    type_desc AS ConstraintType
FROM 
    sys.objects o
-- INNER JOIN 
--     sys.columns c ON o.object_id = c.object_id 
WHERE  
    type_desc LIKE '%CONSTRAINT'
    AND OBJECT_NAME(parent_object_id)= 'All_Data_Types'

Successfully getting table-wise constraint details. But, I want column's information as well.

Could someone help me with this?

Thanks in advance

Upvotes: 3

Views: 1844

Answers (4)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

If you are on SQL Server 2012 or later then you can use system view sys.sysconstraints:

Contains mappings of constraints to the objects that own the constraints within the database.

So, this query:

SELECT *
FROM sys.sysconstraints s
INNER JOIN sys.objects o ON o.object_id = s.constid

theoretically returns the same number of rows as your query. But now you have the additional information about the ID of the column on which the constraint is defined. That is column colid of sys.sysconstraints:

ID of the column on which the constraint is defined.

0 = Table constraint

Thus, using this query:

SELECT OBJECT_NAME(o.object_id) AS ConstraintName,
       SCHEMA_NAME(o.schema_id) AS SchemaName,
       OBJECT_NAME(o.parent_object_id) AS TableName,
       o.type_desc AS ConstraintType,
       COALESCE(c.COLUMN_NAME, 'Table constraint') AS ColumnName
FROM sys.sysconstraints s
INNER JOIN sys.objects o ON o.object_id = s.constid
LEFT JOIN INFORMATION_SCHEMA.COLUMNS c ON c.ORDINAL_POSITION = s.colid AND s.colid <> 0

you also get the name the column related to the constraint.

Upvotes: 2

Rade Vojvodic
Rade Vojvodic

Reputation: 144

For table constraints you could use something like this:

SELECT KCU.*, TC.CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC ON TC.CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA AND TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME

Upvotes: 2

Rade Vojvodic
Rade Vojvodic

Reputation: 144

Fast solution for default constraint is

    SELECT dc.object_id AS ConstraintID, DC.name AS ConstraintName
      , O.object_id AS TableID, O.name AS TableName
      , C.object_id AS ColumnID, C.name AS ColName
FROM sys.default_constraints AS DC
LEFT JOIN sys.objects AS O ON O.object_id = DC.parent_object_id
LEFT JOIN sys.columns c ON o.object_id = c.object_id AND DC.parent_column_id = c.column_id

Upvotes: 4

Muhammad Waheed
Muhammad Waheed

Reputation: 1088

Here is the query. You are using INNER JOIN on wrong column_name of tables. You used this 

INNER JOIN sys.columns c ON o.object_id = c.object_id

I replaced it with 

 INNER JOIN sys.columns c ON o.parent_object_id = c.object_id

Now you can see the column_name as well as table_name along with constraint_details.

SELECT OBJECT_NAME(o.object_id) AS ConstraintName, 
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
c.name as ColumnName,
type_desc AS ConstraintType
FROM sys.objects o
 INNER JOIN sys.columns c ON o.parent_object_id = c.object_id 
WHERE type_desc LIKE '%CONSTRAINT';

Also avoid using the below clause AND OBJECT_NAME(parent_object_id)= 'All_Data_Types'

Upvotes: 1

Related Questions