CODEWITHSUNDEEP
sql-server
mark
mark

Reputation: 62896

How to recognise that a constraint is unnamed in Sql Server?

Motivation - I want to fail our Gated Check-In whenever an unnamed constraint is added.

I could not find any dedicated designation for unnamed constraints in Sql Server. So, it is down to the pattern matching, which I can do in C#.

But what is the pattern? The simplest one that covers most of the cases is to check for "__", but it is not 100% reliable.

So, how would you check that a constraint is explicitly named given its name and having full access to the sys tables?

An alternative and even better solution would be if there is a way to disable unnamed constraints in the first place, but only for the current session.

Upvotes: 7

Views: 1895

Answers (2)

Ruslan
Ruslan

Reputation: 10147

I've one this as a unit test. The following query should not return any rows. Added parent object name to @Martin Smith's query & excluded User-defined Table types:

WITH T
     AS (SELECT is_system_named, name, type_desc, parent_object_id
         FROM   sys.check_constraints
         UNION ALL
         SELECT is_system_named, name, type_desc, parent_object_id
         FROM   sys.default_constraints
         UNION ALL
         SELECT is_system_named, name, type_desc, parent_object_id
         FROM   sys.key_constraints
         UNION ALL
         SELECT is_system_named, name, type_desc, parent_object_id
         FROM   sys.foreign_keys)
SELECT  o.name,
        o.type_desc,
        T.name,
        T.type_desc
FROM   T
INNER JOIN sys.objects o ON o.object_id = T.parent_object_id
WHERE  T.is_system_named = 'true' 
AND o.type_desc <> 'TYPE_TABLE'
AND o.name <> '__RefactorLog'
ORDER BY o.name

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 454020

I could not find any dedicated designation for unnamed constraints in Sql Server

It is there. You can use the below

WITH T
     AS (SELECT is_system_named, name, type_desc
         FROM   sys.check_constraints
         UNION ALL
         SELECT is_system_named, name, type_desc
         FROM   sys.default_constraints
         UNION ALL
         SELECT is_system_named, name, type_desc
         FROM   sys.key_constraints
         UNION ALL
         SELECT is_system_named, name, type_desc
         FROM   sys.foreign_keys)
SELECT name,
       type_desc
FROM   T
WHERE  is_system_named = 'true'

Upvotes: 12

Related Questions