How to find default constraints (DF_) in SQL Server

When you create a column with a default value, SQL Server creates a default constraint (DF_). For example, let’s say that we have a table called Consultations with a nullable column IsOnAggregation whose default value is 0.

If for any reason we want to remove the column, SQL Server will complain because there is a constraint attached to the column. What we have to do is to remove the constraint first, and then the column:


ALTER TABLE [Consultation]
DROP DF__Consultat__IsOnA__571DF1D5
GO
                
alter table [Consultation]
drop column IsOnAggregation
GO

We can do this easily, true. But what di we do if we have to prepare an update script for a production database for which we do not have the rights to alter database objects? We can test the query locally, but the name of the constraint on the production database will be different. Well, the solution is to query the production database to discover the name of the constraint, so that we can use it in our script that a DBA will execute on the production database (note: we are assuming we do not have permissions to alter the database structure but we have permissions to query the database). The query finds the name of the desired default constraint by exploring the content of the table sysobjects:


SELECT * 
FROM sysobjects so 
    INNER JOIN syscolumns sc ON so.id = sc.cdefault
    INNER JOIN sysobjects soi ON sc.id = soi.id
WHERE so.xtype = 'D' and sc.name = 'IsOnAggregation' and soi.name = 'Consultation'

Now that we know the name of the constraint, we can prepare our script for the DBAs!