oracle去掉默认值sql,如何在T-SQL中删除默认值或类似约束？

这里有我自己的版本，它会删除所有依赖的约束 – 默认约束 (如果存在的话)和所有受影响的检查约束 (正如SQL标准似乎暗示的一样，和其他一些数据库似乎是这样)

declare @constraints varchar(4000); declare @sql varchar(4000); with table_id_column_position as ( select object_id table_id, column_id column_position from sys.columns where object_id is not null and object_id = object_id('TableName') and name = 'ColumnToBeDropped' ) select @constraints = coalesce(@constraints, 'constraint ') + '[' + name + '], ' from sysobjects where ( -- is CHECK constraint type = 'C' -- dependeds on the column and id is not null and id in ( select object_id --, object_name(object_id) from sys.sql_dependencies, table_id_column_position where object_id is not null and referenced_major_id = table_id_column_position.table_id and referenced_minor_id = table_id_column_position.column_position ) ) OR ( -- is DEFAULT constraint type = 'D' and id is not null and id in ( select object_id from sys.default_constraints, table_id_column_position where object_id is not null and parent_object_id = table_id_column_position.table_id and parent_column_id = table_id_column_position.column_position ) ); set @sql = 'alter table TableName drop ' + coalesce(@constraints, '') + ' column ColumnToBeDropped'; exec @sql

(注意：在上面的代码中， TableName和ColumnToBeDropped都会出现两次)

这通过构build单个ALTER TABLE TableName DROP CONSTRAINT c1, ..., COLUMN ColumnToBeDropped并执行它。