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]; |
