This page looks best with JavaScript enabled

MS SQL drop column with default constraint

 ·   ·  ☕ 1 min read

The following MS SQL script will generate MS SQL statements to drop a list of columns with default constraint for a certain table.

Need to change table_name and list of columns, as applicable. Statements will be generated only if required.

To make things clear:

  • If column does not exist within specified table, no drop will be generated.
  • If column does not have a default constraint, no drop for this constraint will be generated.
  • The script does not depend on default constraint name.
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
SELECT CASE WHEN t.object_type = 'D' THEN
   'ALTER TABLE ' + t.table_name + ' DROP CONSTRAINT ' + object_name + '; ' ELSE '' END
 + 'ALTER TABLE ' + t.table_name + ' DROP COLUMN ' + t.column_name + ';' FROM (
SELECT p.name AS table_name, cdef.name AS column_name, c.name AS object_name, c.type AS object_type
FROM sys.columns cdef
LEFT JOIN sys.objects p ON cdef.object_id = p.object_id
LEFT JOIN sys.sysconstraints con ON cdef.object_id = con.id AND cdef.column_id = con.colid
LEFT JOIN sys.objects c ON c.parent_object_id = p.object_id AND c.object_id = con.constid ) t
WHERE t.table_name = 'tablename'
  AND t.column_name IN ('column1','column2','column3')

Victor Zakharov
WRITTEN BY
Victor Zakharov
Web Developer (Angular/.NET)