Mfcttrf

Sooner or later, a db developer will come across, and possibly use, a naming scheme for many objects like foreign keyes, default constraints etc.

Let's consider this example: Name all foreign keys as:
FK__[name of source table]__[name of source col]__ref__[name of master table]__[name of master column]

What kind of parametrization/external programs could be used to achieve having such a name as default, when for example using a create table statements which mentions the fk but does not explicitly provide a constraint name?

What kind of parametrization/external programs could be used to
achieve having such a name as default, when for example using a create
table statements which mentions the fk but does not explicitly provide
a constraint name?

There's no way to change the default name generated when the DDL doesn't contain a name. The only thing you can do is to run a batch process to identify non-compliant objects and perhaps drop and recreate them.

If you want to discourage creation of auto-named constraints, you can do it using database-level DDL trigger for CREATE_TABLE and ALTER_TABLE events.

Such as, for example

CREATE TRIGGER [CheckConstraintsNaming] ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE
AS
BEGIN
 SET NOCOUNT ON;

 DECLARE @event xml, @e_type varchar(30), @s_name sysname, @o_name sysname, @o_id int, @is_filetable bit;
 SET @event = EVENTDATA();
 SET @e_type = @event.value('(/EVENT_INSTANCE/EventType/text())[1]', 'varchar(30)');
 SET @s_name = @event.value('(/EVENT_INSTANCE/SchemaName/text())[1]', 'sysname');
 SET @o_name = @event.value('(/EVENT_INSTANCE/ObjectName/text())[1]', 'sysname');
 SELECT @o_id = t.object_id, @is_filetable = t.is_filetable
 FROM sys.tables t
 JOIN sys.schemas s ON s.schema_id = t.schema_id
 WHERE s.name = @s_name AND t.name = @o_name;

 IF @is_filetable = 1
 RETURN;

 DECLARE @msgConstraints nvarchar(max), @msg nvarchar(max);

 SELECT
 @msgConstraints = STRING_AGG(QUOTENAME(c.name), CHAR(13) + CHAR(10))
 FROM (
 SELECT name FROM sys.key_constraints WHERE parent_object_id = @o_id AND is_system_named = 1
 UNION ALL
 SELECT name FROM sys.foreign_keys WHERE parent_object_id = @o_id AND is_system_named = 1
 UNION ALL
 SELECT name FROM sys.check_constraints WHERE parent_object_id = @o_id AND is_system_named = 1
 UNION ALL
 SELECT name FROM sys.default_constraints WHERE parent_object_id = @o_id AND is_system_named = 1
 ) c(name)
 WHERE @e_type = 'CREATE_TABLE'
 OR @e_type = 'ALTER_TABLE'
 AND @event.exist('/EVENT_INSTANCE/AlterTableActionList/Create/Constraints/Name[text()=sql:column("c.name")]') = 1;

 IF @msgConstraints IS NULL OR @msgConstraints = ''
 RETURN;

 SET @msg = 'Table ' + QUOTENAME(@s_name) + '.' + QUOTENAME(@o_name) + ' is being '
 + LOWER(LEFT(@e_type, 5)) + 'ed with one or more auto named constraints:'
 + CHAR(13) + CHAR(10) + @msgConstraints
 + CHAR(13) + CHAR(10) + 'Please specify constraint names explicitly.';

 THROW 51011, @msg, 1;
 ROLLBACK TRANSACTION;
END
GO

Once you have it in database, things like

CREATE TABLE TableName
(
 id int NOT NULL,
 parent_id int NULL,
 name varchar(20) NOT NULL,
 dateCreated datetime NOT NULL DEFAULT (GETDATE()),
 value1 float NOT NULL,
 value2 tinyint NOT NULL,
 PRIMARY KEY (ID),
 FOREIGN KEY (parent_id) REFERENCES TableName,
 UNIQUE (name),
 CHECK ((value1 BETWEEN 0 AND 1) AND (value2 IN (1, 2, 4, 8)))
);

(and equivalent ALTER) will not be allowed.

The error message thrown

Msg 51011, Level 16, State 1, Procedure ConstraintNameCheck, Line 45
[Batch Start Line 50]

Table [dbo].[TableName] is being created with one or more auto named constraints:

[PK__TableNam__3213E83FB1491772]

[UQ__TableNam__72E12F1BE94335EB]

[FK__TableName__paren__316D4A39]

[CK__TableName__32616E72]

[DF__TableName__dateC__30792600]

Please specify constraint names explicitly.

will remind you or your colleague to name constraints explicitly. It can be modified to suggest the "right" name additionally.

Those who want to see it as a database option in future releases of SQL Server, such as

ALTER DATABASE [DbName]
 SET CONSTRAINT_NAMING = EXPLICIT ;

can cast a vote for my initiative here.