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. |
Tech Blog >