CODEWITHSUNDEEP
sql-server
user3756799
user3756799

Reputation: 91

How status is calculated from sys.sysconstraints?

select * from sys.sysconstraints

i am getting status as 

| id         |status|
|------------|------|
|<object_id> | 2593 |
|<object_id> | 3106 |

Can anyone tell how status is calculated in SQL Server?

Upvotes: 3

Views: 814

Answers (2)

Martin Smith
Martin Smith

Reputation: 453563

You can use exec sp_helptext 'sys.sysconstraints' to see exactly how it is calculated.

This shows you the definition of the view.

CREATE VIEW sys.sysconstraints AS  
 SELECT constid = object_id,  
  id = parent_object_id,  
  colid = convert(smallint, property),  
  spare1 = convert(tinyint, 0),  
  status = convert(int,  
    CASE type  
     WHEN 'PK' THEN 1 WHEN 'UQ' THEN 2 WHEN 'F ' THEN 3  
     WHEN 'C ' THEN 4 WHEN 'D ' THEN 5 ELSE 0 END  
    + CASE WHEN property <> 0 THEN (16) ELSE (32) END  
    + CASE WHEN ObjectProperty(object_id, 'CnstIsClustKey') <> 0  
      THEN (512) ELSE 0 END  
    + CASE WHEN ObjectProperty(object_id, 'CnstIsNonclustKey') <> 0  
      THEN (1024) ELSE 0 END  
    + (is_system_named * 131072)  
    + (2048)      -- CNST_NOTDEFERRABLE  
    + (is_disabled * 16384)  
    + (is_not_for_replication * 2097152)),  
  actions = convert(int,  4096),  
  error = convert(int, 0)  
 FROM sys.objects$  
 WHERE (parent_object_id > 0 OR   
  (parent_object_id & 0xe0000000 = 0xa0000000)) -- IsLocalTempObjectId  
  AND type IN ('C ', 'F ', 'PK', 'UQ', 'D ')

So code to reverse this is 

 SELECT *
 FROM (VALUES(2593), 
             (3106)) V(Status)
 CROSS APPLY
 (
 SELECT CASE WHEN Status & 5 = 5 THEN 'D '
             WHEN Status & 4 = 4 THEN 'C '
             WHEN Status & 3 = 3 THEN 'F '
             WHEN Status & 2 = 2 THEN 'UQ'
             WHEN Status & 1 = 1 THEN 'PK '
         END AS type, 
         CASE WHEN Status & 16 = 16 THEN 'column level'
             WHEN Status & 32 = 32 THEN 'table level'
         END AS level, 
         CASE WHEN Status & 512 = 512 THEN 'Clustered key'
             WHEN Status & 1024 = 1024 THEN 'Non clustered key'
         END AS ClustNonClustKey, 
         CASE WHEN Status & 16384 = 16384 THEN 'true' ELSE 'false' END
         AS is_disabled, 
         CASE WHEN Status & 131072 = 131072 THEN 'true' ELSE 'false' END
         AS is_system_named, 
         CASE WHEN Status & 2097152 = 2097152 THEN 'true' ELSE 'false' END
         AS is_not_for_replication
)CA

Which returns

+--------+------+-------------+-------------------+-------------+-----------------+------------------------+
| Status | type |    level    | ClustNonClustKey  | is_disabled | is_system_named | is_not_for_replication |
+--------+------+-------------+-------------------+-------------+-----------------+------------------------+
|   2593 | PK   | table level | Clustered key     | false       | false           | false                  |
|   3106 | UQ   | table level | Non clustered key | false       | false           | false                  |
+--------+------+-------------+-------------------+-------------+-----------------+------------------------+

Upvotes: 4

Lukasz Szozda
Lukasz Szozda

Reputation: 175924

Based on sys.sysconstraints. It is pseudo-bit-mask indicating the status.

1 = PRIMARY KEY constraint

2 = UNIQUE KEY constraint

3 = FOREIGN KEY constraint

4 = CHECK constraint

5 = DEFAULT constraint

16 = Column-level constraint

32 = Table-level constraint

Anyway depending what are you trying to achieve, I would recommend to use INFORMATION_SCHEMA or specific constraint view, instead of trying to parse this deprecated sys view.

Upvotes: 1

Related Questions