A few weeks ago, I ran into a question on one of the forums asking for a script that can help the team validate object naming conventions. Immediately, I was able to sympathize with the team.
What happens is that when developers use the graphical (GUI) tools in the SQL Server Management Studio (SSMS) or via a simple script, they often fail to specify a name to each individual constraint. These slips are not intentional – developers don’t often realize that each constraint is an independent object because they are ultimately related to another user defined object (a table).
However, when a name is not explicitly specified for a particular constraint, what Microsoft SQL Server does is provide a name by combining the following:
- A standard prefix indicating the object (e.g. “DF” for default constraints)
- 9 characters of the object name
- 5 characters of the field name
- Finally, the unique Id of the object, represented in hexa-decimal format
While this format will always generate a unique value, it would generate names that may not be intuitive. It is therefore a common practice to review the database code and review for compliance with naming conventions that have been defined in the product/project.
This logic can be leveraged during code reviews/audits to identify objects where standard project naming conventions are not met.
To demonstrate the functionality of the script, I create one table with a wide range of constraints – none of which have a name specified.
IF OBJECT_ID('dbo.ConstraintsWithoutNames','U') IS NOT NULL
DROP TABLE dbo.ConstraintsWithoutNames;
CREATE TABLE dbo.ConstraintsWithoutNames
([RecordId] INT NOT NULL IDENTITY(1,1)
PRIMARY KEY CLUSTERED,
[RecordName] VARCHAR(255) NULL,
[RecordStatus] TINYINT NOT NULL DEFAULT (0)
CHECK ([RecordStatus] IN (0, 2, 4, 8))
Now, the following script is a simple string search that looks for strings ending with the hexa-decimal representation of the parent object.
FROM [sys].[objects] AS [so]
WHERE [so].[is_ms_shipped] = 0 –Considering user objects only
AND [so].[name] LIKE ('%' + REPLACE(CONVERT(NVARCHAR(255),CAST([so].[object_id] AS VARBINARY(MAX)),1),'0x',''))
–Only those objects whose names end with the hexadecimal
–representation of their object Id
I hope you found this script useful. Please do share your ideas/scripts that you may be using in your day-to-day activities.
Until we meet next time,
Be courteous. Drive responsibly.