Exclusão de colunas com valor default

Ao excluir uma coluna com valor default atribuído em uma tabela, o seguinte erro é disparado pelo SQL Server:

The object 'DF__Contato__ativo__6166761E' is dependent on column 'Ativo'.

Isso acontece porque antes de excluir uma coluna de uma tabela, é necessário certificar-se que ela não é referenciada por nenhuma chave ou restrição e também não tenha um valor default associado a ela.

Para resolver este problema, pode-se utilizar a stored procedure de sistema sp_unbindefault . Veja o exemplo abaixo:

-- Criando o valor default para o banco de dados
CREATE DEFAULT DF_Ativo AS 0
GO

-- Criando tabela sem o valor default associado
CREATE TABLE Contato
(
id int identity primary key
,nome varchar(50) not null
,ativo bit
)

-- Associando o valor default à coluna Ativo da tabela
EXEC sp_bindefault 'DF_Ativo','Contato.ativo'

-- Removendo o valor default da coluna Ativo
EXEC sp_unbindefault 'Contato.Ativo'

-- Excluindo coluna Ativo
ALTER TABLE Contato
DROP COLUMN Ativo

-- Verificando se a coluna foi excluída
SELECT * FROM Contato

O código acima retorna as seguintes mensagens:

Default bound to column.
Default unbound from table column.

E o comando SELECT retorna o seguinte resultado:

id nome
----------- --------------------------------------------------

Entretanto, esta procedure funciona apenas se o valor default foi associado através da stored procedure sp_bindefault. Quando um valor default é atribuído durante a criação da tabela ou através do comando ALTER TABLE, a procedure sp_unbindefault retorna uma mensagem de erro como abaixo:

Cannot unbind from 'Contato.Ativo'. Use ALTER TABLE DROP CONSTRAINT.

Seria fácil executar o comando ALTER se soubéssemos o nome da restrição gerada automaticamente pelo SQL Server.

Assim, para facilitar esta tarefa implementei o script abaixo. Ele remove a restrição de default utilizando apenas o nome da tabela e o da coluna.
Para utilizá-lo, basta configurar as variáveis @nomeTabela e @nomeColuna, conforme indicado:

DECLARE @nomeTabela VARCHAR(50)
DECLARE @nomeColuna VARCHAR(50)

/* Configure aqui */

   SET @nomeTabela = 'Contato'
   SET @nomeColuna = 'Ativo'

/* Fim das configurações */

DECLARE @command NVARCHAR(1000)

SELECT
    @command='ALTER TABLE '+@NomeTabela+' DROP CONSTRAINT '+D.name
FROM
    sysobjects D
        inner join sysobjects T on
           T.id = D.parent_obj
        inner join syscolumns C on
           C.id = T.id AND cdefault = D.id
WHERE
    T.name = @nomeTabela
    AND C.name = @NomeColuna
    AND D.xtype = 'D'

exec sp_executesql @command

Este script utiliza as views de sistema sysobjects e syscolumns para encontrar o nome da restrição que atribui o valor default à coluna. Com este valor, o comando de exclusão da restrição é gerado e executado.

Este script foi útil para você? Deixe o seu feedbacks abaixo através de comentários!

Até a próxima!