Tech Blog‎ > ‎

MS SQL Drop Column With Default Constraint

posted Oct 4, 2011, 8:59 AM by Victor Zakharov   [ updated Oct 4, 2011, 9:02 AM ]
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.

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')


Please direct all comments to my email.
Comments