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