Create procedure DropDefaultConstraint
@Tabla varchar(100),
@Campo varchar(100)
as
Declare @Constrain varchar(250)
Declare @dato varchar(250)
begin
if Exists(SELECT d.name
FROM sys.default_constraints AS d
INNER JOIN sys.columns AS c ON d.parent_object_id = c.object_id AND d.parent_column_id = c.column_id
WHERE d.parent_object_id = OBJECT_ID(@Tabla, N'U') AND c.name = @Campo)
begin
select 'S'
--Si existe recupera el nombre del constrain
SELECT @Constrain = d.name
FROM sys.default_constraints AS d
INNER JOIN sys.columns AS c ON d.parent_object_id = c.object_id AND d.parent_column_id = c.column_id
WHERE d.parent_object_id = OBJECT_ID(@Tabla, N'U') AND c.name = @Campo
--Scryp dinámico para eliminar el constrain recuperado
set @dato = 'Alter table ' + @Tabla + ' drop constraint ' + @Constrain
--Ejecuta la sentencia de eliminación del constrain
exec(@dato)
--Elimina la columna
set @dato = 'Alter table ' + @Tabla + ' drop column ' + @Campo
exec(@dato)
end
end
--Para usarlo
DropDefaultConstraint 'Tutabla','TuColumna'