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.
- 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,