Colonne de suppression SQL Server 2005 avec contraintes

Question

J'ai une colonne avec un "DÉFAUT" de la contrainte. J'aimerais créer un script qui sera en baisse cette colonne.

Le problème est qu'il retourne cette erreur:

Msg 5074, Level 16, State 1, Line 1  
The object 'DF__PeriodSce__IsClo__4BCC3ABA' is dependent on column 'IsClosed'. 
Msg 4922, Level 16, State 9, Line 1 
ALTER TABLE DROP COLUMN IsClosed failed because one or more objects access this column.

Je ne pouvais pas trouver un moyen facile de supprimer une colonne et toutes ses contraintes associées (seulement trouvé grand scripts qui regarde dans la table système... il DOIT y (!!) être un "bon" moyen de le faire.)

Et comme la contrainte par DÉFAUT le nom a été généré de façon aléatoire, je peux pas le laisser tomber par nom.

---

Mise à jour :
Le type de contrainte est "par DÉFAUT".

J'ai vu les solutions que vous avez proposées, mais je les trouve tous vraiment "sale"... vous Ne pensez pas? Je ne sais pas si c'est avec Oracle ou MySQL, mais il est possible de faire quelque chose comme:

DROP COLUMN xxx CASCADE CONSTRAINTS 

Et il supprime toutes les contraintes qui y sont liées... Ou au moins, il va automatiquement les contraintes mappé à la colonne (au moins VÉRIFIER les contraintes!)

Est-il rien de tout cela dans MSSQL?

Answer 1

Voici un script qui supprimera la colonne avec sa contrainte par défaut. Remplacez MYTABLENAME et MYCOLUMNNAME manière appropriée.

 declare @constraint_name sysname, @sql nvarchar(max)

select @constraint_name = name 
from sys.default_constraints 
where parent_object_id = object_id('MYTABLENAME')
AND type = 'D'
AND parent_column_id = (
    select column_id 
    from sys.columns 
    where object_id = object_id('MYTABLENAME')
    and name = 'MYCOLUMNNAME'
    )

set @sql = N'alter table MYTABLENAME drop constraint ' + @constraint_name
exec sp_executesql @sql

alter table MYTABLENAME drop column MYCOLUMNNAME

go
 

Answer 2

Cette requête trouve les contraintes par défaut pour une table donnée. C'est pas joli, je suis d'accord:

 select 
    col.name, 
    col.column_id, 
    col.default_object_id, 
    OBJECTPROPERTY(col.default_object_id, N'IsDefaultCnst') as is_defcnst, 
    dobj.name as def_name
from sys.columns col 
    left outer join sys.objects dobj 
        on dobj.object_id = col.default_object_id and dobj.type = 'D' 
where col.object_id = object_id(N'dbo.test') 
and dobj.name is not null
 

[EDIT] Mis à jour par le commentaire de Julien N

Answer 3

Peut-être que cela pourrait aider un peu plus:

 declare @tablename nvarchar(200)
declare @colname nvarchar(200)
declare @default sysname, @sql nvarchar(max)

set @tablename = 'your table'
set @colname = 'column to drop'

select @default = name 
from sys.default_constraints 
where parent_object_id = object_id(@tablename)
AND type = 'D'
AND parent_column_id = (
    select column_id 
    from sys.columns 
    where object_id = object_id(@tablename)
    and name = @colname 
    )

set @sql = N'alter table ' + @tablename + ' drop constraint ' + @default
exec sp_executesql @sql

set @sql = N'alter table ' + @tablename + ' drop column ' + @colname
exec sp_executesql @sql
 

Il suffit de définir les variables @tablename & @colname pour supprimer la colonne.

Answer 4

Je pense aussi que c'est un défaut dans SQL server pour ne pas avoir une cascade de baisse disponible. J'ai travaillé mon chemin autour d'elle en interrogeant les tables système de la même manière que les autres personnes décrites ici:

• INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE répertorie uniquement les clés étrangères, les clés primaires et les contraintes uniques.
• La seule façon de regarder pour les contraintes par défaut est de regarder pour eux dans sys.default_constraints.
• ce qui n'a pas été mentionné ici encore, c'est que les indices de également faire de la suppression d'une colonne d'échouer, de sorte que vous devez également supprimer tous les index de votre colonne avant de procéder à la suppression d'une colonne.

Le script obtenu n'est pas assez, mais je l'ai mis dans une procédure stockée pour être en mesure de les réutiliser:

CREATE PROCEDURE DropColumnCascading @tablename nvarchar(500), @columnname nvarchar(500)
AS

SELECT CONSTRAINT_NAME, 'C' AS type
INTO #dependencies
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = @tablename AND COLUMN_NAME = @columnname

INSERT INTO #dependencies
select d.name, 'C'
from sys.default_constraints d
join sys.columns c ON c.column_id = d.parent_column_id AND c.object_id = d.parent_object_id
join sys.objects o ON o.object_id = d.parent_object_id
WHERE o.name = @tablename AND c.name = @columnname

INSERT INTO #dependencies
SELECT i.name, 'I'
FROM sys.indexes i
JOIN sys.index_columns ic ON ic.index_id = i.index_id and ic.object_id=i.object_id
JOIN sys.columns c ON c.column_id = ic.column_id and c.object_id=i.object_id
JOIN sys.objects o ON o.object_id = i.object_id
where o.name = @tableName AND i.type=2 AND c.name = @columnname AND is_unique_constraint = 0

DECLARE @dep_name nvarchar(500)
DECLARE @type nchar(1)

DECLARE dep_cursor CURSOR
FOR SELECT * FROM #dependencies

OPEN dep_cursor

FETCH NEXT FROM dep_cursor 
INTO @dep_name, @type;

DECLARE @sql nvarchar(max)

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 
        CASE @type
            WHEN 'C' THEN 'ALTER TABLE [' + @tablename + '] DROP CONSTRAINT [' + @dep_name + ']'
            WHEN 'I' THEN 'DROP INDEX [' + @dep_name + '] ON dbo.[' + @tablename + ']'
        END
    print @sql
    EXEC sp_executesql @sql
    FETCH NEXT FROM dep_cursor 
    INTO @dep_name, @type;
END

DEALLOCATE dep_cursor

DROP TABLE #dependencies

SET @sql = 'ALTER TABLE [' + @tablename + '] DROP COLUMN [' + @columnname + ']'

print @sql
EXEC sp_executesql @sql

Answer 5

> select CONSTRAINT_NAME from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
> WHERE TABLE_NAME = '<tablename>' AND COLUMN_NAME = 'IsClosed'

Ce n'est pas la bonne solution, comme il est expliqué ici : http://msdn.microsoft.com/en-us/library/aa175912.aspx que :

Malheureusement, le nom de la colonne par défaut contrainte n'est pas maintenue dans la norme ANSI COLONNES de la vue, de sorte que vous devez retourner dans les tables système pour trouver le nom

Le seul moyen que j'ai trouvé pour obtenir le nom de la contrainte par DÉFAUT de cette demande :

select  
    t_obj.name 				as TABLE_NAME
    ,c_obj.name				as CONSTRAINT_NAME
    ,col.name				as COLUMN_NAME

from    sysobjects	c_obj
join    sysobjects	t_obj on c_obj.parent_obj = t_obj.id  
join    sysconstraints con on c_obj.id  = con.constid
join    syscolumns	col on t_obj.id = col.id
    		and con.colid = col.colid
where
    c_obj.xtype	= 'D'

Suis-je le seul à trouver ça fou d'être incapable de supprimer facilement une contrainte qui ne concerne que les colonnes je suis en train de tomber ?
J'ai besoin d'exécuter une requête avec 3 rejoint juste pour obtenir le nom de...