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.
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'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With