A couple of months ago, I read a post from Madhivanan [B|T] which taught me how to drop multiple tables in a single DROP TABLE statement [Link]. Last week I was working on a deployment script when I it hit me that I could drop multiple constraints (and columns) at the same time using the ALTER TABLE…DROP statement.

This method works on check, default and unique constraints.

Here’s a simple example:

USE AdventureWorks2012;
GO
ALTER TABLE HumanResources.Employee
    DROP CONSTRAINT CK_Employee_BirthDate,
                    CK_Employee_Gender,
                    CK_Employee_HireDate,
                    CK_Employee_MaritalStatus,
                    CK_Employee_SickLeaveHours,
                    CK_Employee_VacationHours,
                    DF_Employee_CurrentFlag,
                    DF_Employee_ModifiedDate,
                    DF_Employee_rowguid,
                    DF_Employee_SalariedFlag,
                    DF_Employee_SickLeaveHours,
                    DF_Employee_VacationHours;
GO

You can write a similar script for dropping multiple columns in the same manner.

Further Reading

• ALTER TABLE [MSDN/BOL Link]
• Drop multiple tables in a single DROP TABLE statement [Link from Madhivanan [B|T]]
• Leveraging constraint evaluation sequence in SQL Server [Link]

Until we meet next time,

Be courteous. Drive responsibly.