quarta-feira, 14 de setembro de 2011

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!

2 comentários:

Anônimo disse...

Evandro, primeiramente obrigado por postar este script.

Estou com um problema ao utilizá-lo, preciso retirar as restrições de colunas em uma tabela porém ela apresenta erro em alguns casos.

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

SET @nomeTabela = 'Vaga'
SET @nomeColuna = 'vgObs Financeiro'

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

Apresenta o seguinte erro
"Mensagem 102, Nível 15, Estado 1, Linha 1
Incorrect syntax near 'Fina__4D4A6ED8'."

Presumo que seja pelo motivo de que no nome desta restrição esteja com um espaço antes do 'Fina__4D4A6ED8'

ATT. Ricardo Feio

Wilton Queiroz disse...

Montei esse script pode ser util para alguem..

e para remover as colunas rowguid que ficam no banco depois que o mesmo e replicado, a remoção da replicacao nao remove esses campos, que fica so ocupando espaco no disco.


--by wilton queiroz 31/12/2015
--apagar constrains do rowguid
SELECT 'alter table '+o1.name+' DROP CONSTRAINT '+o.name Comando
FROM sysobjects o INNER JOIN SYSOBJECTS O1 ON O1.ID=O.parent_obj
INNER JOIN syscolumns C ON c.id=o1.id
WHERE O.xtype='D' AND c.name='rowguid'

UNION ALL

--apagar indices do rowguid (todo indice rowguid criado pela replicacao comeca com index_XXXX)

SELECT 'DROP INDEX '+c.name+' on '+o.name
FROM sys.sysindexes c INNER JOIN sys.sysobjects o ON o.id=c.id WHERE c.name LIKE 'index_%'
UNION all

--apagar columnas rowguid

SELECT 'alter table '+o.name+' drop column '+c.name
FROM sysobjects o INNER JOIN syscolumns c ON o.id=c.id
WHERE o.xtype='u' AND c.name='rowguid'

--pegar o resultado do script, copiar e colar em nova janela e executar.



Postar um comentário