Names for constraints are optional meaning that if you don’t provide a name when it’s created or cannot afford one, one will be appointed to you by the system.
These system provided names are messy things and I don’t think I have to discourage you from using them. Kenneth Fisher has already done that in Constraint names, Say NO to the default.
But how do you know whether you have any?
Here’s How You Check
SELECT SCHEMA_NAME(schema_id) AS [schema name], OBJECT_NAME(object_id) AS [system generated object name], OBJECT_NAME(parent_object_id) AS [parent object name], type_desc AS [object type] FROM sys.objects WHERE OBJECT_NAME(object_id) LIKE type + '\_\_' + LEFT(OBJECT_NAME(parent_object_id),8) + '\_\_%' ESCAPE '\' OR OBJECT_NAME(object_id) LIKE REPLACE(sys.fn_varbintohexstr(CAST(object_id AS VARBINARY(MAX))), '0x', '%\_\_') ESCAPE '\' |
This will find all your messy system-named constraints.
For example, a table defined like this:
create table MY_TABLE ( id INT IDENTITY PRIMARY KEY, id2 INT REFERENCES MY_TABLE(id) DEFAULT 0, UNIQUE(id), CHECK (id >= 0) ) |
Will give results like this:
Happy hunting.
Update: April 9, 2018
We can get this info from the system views a little easier as Rob Volk pointed out. I’ve also included the parent object’s type.
SELECT OBJECT_SCHEMA_NAME(id) AS [schema name], OBJECT_NAME(constid) AS [system generated constraint name], (select type_desc from sys.objects where object_id = constid) as [constraint type], OBJECT_NAME(id) AS [parent object name], (select type_desc from sys.objects where object_id = id) as [parent object type] FROM sys.sysconstraints WHERE status & 0x20000 > 0 AND OBJECT_NAME(id) NOT IN (N'__RefactorLog', N'sysdiagrams') ORDER BY [parent object type], [parent object name], [system generated constraint name]; |
Here’s an alternative in case Microsoft changes the naming scheme:
with system_constraints(parent_id, [system generated object name], object_type) as (
select parent_object_id, name, type_desc from sys.check_constraints where is_system_named=1
union all select parent_object_id, name, type_desc from sys.key_constraints where is_system_named=1
union all select parent_object_id, name, type_desc from sys.foreign_keys where is_system_named=1
union all select parent_object_id, name, type_desc from sys.default_constraints where is_system_named=1)
select OBJECT_SCHEMA_NAME(parent_id) [schema name],
[system generated object name],
object_name(parent_id) [parent object name],
object_type
from system_constraints
Comment by Robert Volk — April 6, 2018 @ 10:51 am
Here is a slight modification to eliminate the __RefactorLog and sysdiagrams tables from the results.
SELECT
SCHEMA_NAME([o].[schema_id]) AS [schema name],
OBJECT_NAME([o].[object_id]) AS [system generated object name],
OBJECT_NAME([o].[parent_object_id]) AS [parent object name],
[o].[type_desc] AS [object type]
FROM [sys].[objects] AS [o]
WHERE OBJECT_NAME([o].[parent_object_id]) NOT IN (N’__RefactorLog’, N’sysdiagrams’)
AND (OBJECT_NAME([o].[object_id]) LIKE [o].[type] + ‘\_\_’ + LEFT(OBJECT_NAME([o].[parent_object_id]), 8)
+ ‘\_\_%’ ESCAPE ‘\’
OR OBJECT_NAME([o].[object_id]) LIKE REPLACE(
[sys].[fn_varbintohexstr](CAST([o].[object_id] AS VARBINARY(MAX))),
‘0x’,
‘%\_\_’)ESCAPE ‘\’);
Comment by Ton Green — April 6, 2018 @ 2:23 pm
Oh wow! I didn’t know those columns were there.
But it’s annoying that they’re not exposed in sys.constraints eh? Or are they?
I looked at the query plan details of a query that looks at the column is_system_named and found that it’s determined by bit 0x4 in the bitmask “status” in the system table sys.sysschobjs.
Looking around a bit more, I see that bit 0x4 of that table is exposed as bit 0x20000 in sys.sysconstraints.
And when we factor in Ton’s improvements, we get:
SELECT SCHEMA_NAME(o.schema_id) AS [schema name], OBJECT_NAME(o.object_id) AS [system generated object name], OBJECT_NAME(o.parent_object_id) AS [parent object name], o.type_desc AS [object type] FROM sys.objects o JOIN sys.sysconstraints c ON o.object_id = c.constid WHERE status & 0x20000 > 0 AND OBJECT_NAME(o.parent_object_id) NOT IN (N'__RefactorLog', N'sysdiagrams')Comment by Michael J. Swart — April 6, 2018 @ 3:29 pm
I also found that TVF’s create system named constraints and you can’t name them yourself. 🙁
From Microsoft documentation:
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql
::= and ::=
Defines the constraint for a specified column or table. For CLR functions, the only constraint type allowed is NULL. Named constraints are not allowed.
Comment by Tony Green — April 6, 2018 @ 3:53 pm
[…] Michael J. Swart has a script which helps you find system-generated constraint names: […]
Pingback by Finding System-Generated Constraint Names – Curated SQL — April 9, 2018 @ 8:00 am
Hi Tony,
It would be good to have names for everything, but I definitely wanted to start with objects that can be altered or dropped because those are the objects that are enemies of scripting changes and deployments. That consistent scripting becomes especially important when databases are deployed in multiple places.
For example, if I want to drop a constraint in all my databases, I can write a straightforward DROP statement only when that constraint is named.
Comment by Michael J. Swart — April 9, 2018 @ 10:48 am
[…] Read More (Community […]
Pingback by Are There Any System Generated Constraint Names Lurking In Your Database? - SSWUG.ORG — April 27, 2018 @ 3:00 am
It’s an old post but I query like this (adding o.is_ms_shipped = 0 strips out any system created objects):
SELECT
[Schema] = SCHEMA_NAME(o.schema_id),
[System Generated Name] = OBJECT_NAME(o.object_id),
[Parent Name] = OBJECT_NAME(o.parent_object_id),
[object type] = o.type_desc
FROM
sys.objects o
JOIN sys.sysconstraints c ON o.object_id = c.constid
WHERE
(status & 0x20000) > 0
AND o.is_ms_shipped = 0
Comment by Mitch Wheat — April 11, 2020 @ 9:46 pm
[…] Michael J Swart describes a query to discover the system generated names in your databases (with a small modification): […]
Pingback by Do You Name All Your SQL Server Database Constraints? – Mitch Wheat — April 11, 2020 @ 11:28 pm