How to check whether a default constraint with no name exist on a column

Question

I have a table with a default constraint defined as below.

CREATE TABLE [dbo].[Test](
    [LicenseKey] [int] NULL,
    [CreatedOnDate] [datetime] NOT NULL DEFAULT (getdate())
) ON [PRIMARY]

How to check whether this constraint exist before I run a separate script (like ALTER TABLE ADD CONSTRAINT) to add the constraint?

I referred following post How to check if a Constraint exists in Sql server? But that post handles only names default constraints check.

The link should have given you a clue - there is a table for default constraints aptly named [sys.default_constraints](https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-default-constraints-transact-sql?view=sql-server-ver16). I'll point out that the CreatedOnDate probably should not allow nulls either. — SMor, Aug 08 '22 at 20:24
@SMor I already tried with the scripts mentioned there... It does not list this constraint — LCJ, Aug 08 '22 at 20:27
It has to be there. Every constraint MUST have a name. If you don't provide one then SQL Server will generate one for you automatically and it will be listed in `sys.default_constraints`. Why don't you just give it a name so you don't have to go through this? — squillman, Aug 08 '22 at 20:29

score 3 · Accepted Answer · answered Aug 08 '22 at 20:53

A column can only have one default constraint on it.

You can find the details for any such default constraint with the below query against sys.default_constraints.

DECLARE @TableName NVARCHAR(500) = N'dbo.Test', 
        @ColumnName sysname = N'CreatedOnDate';

SELECT d.definition,
       d.is_system_named, /* 1 if no name given by the user and was allocated a system generated one */
       d.name
FROM   sys.default_constraints AS d
       INNER JOIN sys.columns AS c
               ON d.parent_object_id = c.object_id
                  AND d.parent_column_id = c.column_id
WHERE  d.parent_object_id = OBJECT_ID(@TableName, N'U')
       AND c.name = @ColumnName;

NB: If you later need to re-create the default constraint (perhaps to reference SYSUTCDATETIME instead of GETDATE) the constraint name created by your current CREATE TABLE will be different in all environments and you will have to resort to dynamic SQL to do this. It is advisable to always name your constraints (for permanent tables).

How to check whether a default constraint with no name exist on a column

1 Answers1