Setting up a default column is a common task for  developers.  But, are we naming those default constraints explicitly?

In the below  table creation, for the column, sys_DateTime the default value Getdate() will be allocated.

CREATE TABLE SampleTable
(ID int identity(1,1),
Sys_DateTime Datetime DEFAULT getdate()
)

We can check the relevant information from the system catalogs from following query.

SELECT
sc.name TableName,
dc.name DefaultName,
dc.definition,
OBJECT_NAME(dc.parent_object_id) TableName,
dc.is_system_named
 FROM sys.default_constraints dc
INNER JOIN sys.columns sc
ON
dc.parent_object_id = sc.object_id
AND
dc.parent_column_id = sc.column_id

and results would be:

Most of the above columns are self-explanatory. The last column, is_system_named, is to identify whether the default name was given by the system. As you know, in the above case, since we didn’t provide  any default name, the  system will generate a default name for you.

But the problem with these names is that they can differ from environment to environment.  If example if I create this table in different table the default name could be DF__SampleTab__Sys_D__7E6CC920

Now let us create another default and explicitly name it:

CREATE TABLE SampleTable2
(ID int identity(1,1),
Sys_DateTime Datetime
)
 
ALTER
TABLE SampleTable2
ADD CONSTRAINT DF_sys_DateTime_Getdate
DEFAULT( Getdate()) FOR Sys_DateTime

If we run the previous query again we will be returned the below output.

And you can see that last created default name has 0 for is_system_named.

Now let us say I want to change the data type of the sys_DateTime column to something else:

ALTER TABLE SampleTable2 ALTER COLUMN
Sys_DateTime Date

This will generate the below error:

Msg 5074, Level 16, State 1, Line 1

The object ‘DF_sys_DateTime_Getdate’ is dependent on column ‘Sys_DateTime’.

Msg 4922, Level 16, State 9, Line 1

ALTER TABLE ALTER COLUMN Sys_DateTime failed because one or more objects access this column.

This means, you need to drop the default constraint before altering it:

ALTER TABLE [dbo].[SampleTable2]
DROP CONSTRAINT [DF_sys_DateTime_Getdate]
ALTER TABLE SampleTable2 ALTER COLUMN Sys_DateTime Date
 
ALTER TABLE [dbo].[SampleTable2]
ADD CONSTRAINT [DF_sys_DateTime_Getdate]
DEFAULT (getdate()) FOR [Sys_DateTime]

If you have a system named default constraint that can differ from environment to environment and so you cannot drop it as before, you can use the below code template:

DECLARE @defaultname VARCHAR(255)
DECLARE @executesql VARCHAR(1000)
 
SELECT @defaultname = dc.name
        FROM sys.default_constraints dc
            INNER JOIN sys.columns sc
            ON dc.parent_object_id = sc.object_id
            AND dc.parent_column_id = sc.column_id
            WHERE OBJECT_NAME (parent_object_id) = 'SampleTable'
            AND sc.name ='Sys_DateTime'
SET @executesql =  'ALTER TABLE SampleTable  DROP CONSTRAINT ' + @defaultname
EXEC(   @executesql)
ALTER TABLE SampleTable ALTER COLUMN Sys_DateTime Date
ALTER TABLE [dbo].[SampleTable] ADD DEFAULT (Getdate()) FOR [Sys_DateTime]