Comment supprimer une contrainte SQL par défaut sans connaître son nom ?

Question

Dans Microsoft SQL Server, je connais la requête permettant de vérifier si une contrainte par défaut existe pour une colonne et de supprimer une contrainte par défaut :

IF EXISTS(SELECT * FROM sysconstraints
  WHERE id=OBJECT_ID('SomeTable')
  AND COL_NAME(id,colid)='ColName'
  AND OBJECTPROPERTY(constid, 'IsDefaultCnst')=1)    
ALTER TABLE SomeTable DROP CONSTRAINT DF_SomeTable_ColName

Mais en raison d'une faute de frappe dans les versions précédentes de la base de données, le nom de la contrainte pouvait être DF_SomeTable_ColName ou DF_SmoeTable_ColName .

Comment supprimer la contrainte par défaut sans erreur SQL ? Les noms des contraintes par défaut n'apparaissent pas dans la table INFORMATION_SCHEMA, ce qui rend les choses un peu plus difficiles.

Donc, quelque chose comme "supprimer la contrainte par défaut dans cette table/colonne", ou "supprimer la contrainte par défaut dans cette table/colonne". DF_SmoeTable_ColName mais ne donne pas d'erreur s'il ne peut pas le trouver.

Answer 1

En développant le code de Mitch Wheat, le script suivant va générer la commande pour faire tomber la contrainte et l'exécuter dynamiquement.

declare @schema_name nvarchar(256)
declare @table_name nvarchar(256)
declare @col_name nvarchar(256)
declare @Command  nvarchar(1000)

set @schema_name = N'MySchema'
set @table_name = N'Department'
set @col_name = N'ModifiedDate'

select @Command = 'ALTER TABLE ' + @schema_name + '.[' + @table_name + '] DROP CONSTRAINT ' + d.name
 from sys.tables t
  join sys.default_constraints d on d.parent_object_id = t.object_id
  join sys.columns c on c.object_id = t.object_id and c.column_id = d.parent_column_id
 where t.name = @table_name
  and t.schema_id = schema_id(@schema_name)
  and c.name = @col_name

--print @Command

execute (@Command)

Answer 2

L'article du blog de Rob Farley pourrait vous aider :

• Deux façons de trouver / supprimer une contrainte par défaut sans connaître son nom

Quelque chose comme :

 declare @table_name nvarchar(256)
 declare @col_name nvarchar(256)
 set @table_name = N'Department'
 set @col_name = N'ModifiedDate'

 select t.name, c.name, d.name, d.definition
 from 
     sys.tables t
     join sys.default_constraints d on d.parent_object_id = t.object_id
     join sys.columns c on c.object_id = t.object_id
                           and c.column_id = d.parent_column_id
 where 
     t.name = @table_name
     and c.name = @col_name

Answer 3

J'ai trouvé que cela fonctionne et n'utilise pas de jointures :

DECLARE @ObjectName NVARCHAR(100)
SELECT @ObjectName = OBJECT_NAME([default_object_id]) FROM SYS.COLUMNS
WHERE [object_id] = OBJECT_ID('[tableSchema].[tableName]') AND [name] = 'columnName';
EXEC('ALTER TABLE [tableSchema].[tableName] DROP CONSTRAINT ' + @ObjectName)

Assurez-vous simplement que columnName n'est pas entouré de parenthèses, car la requête recherche une correspondance exacte et ne renverra rien si elle est [columnName].

Answer 4

Pour supprimer la contrainte pour plusieurs colonnes :

declare @table_name nvarchar(256)

declare @Command nvarchar(max) = ''

set @table_name = N'ATableName'

select @Command = @Command + 'ALTER TABLE ' + @table_name + ' drop constraint ' + d.name + CHAR(10)+ CHAR(13)
from sys.tables t
join sys.default_constraints d on d.parent_object_id = t.object_id
join sys.columns c on c.object_id = t.object_id
     and c.column_id = d.parent_column_id
where t.name = @table_name and c.name in ('column1','column2','column3')

--print @Command

execute (@Command)

Answer 5

Solution élargie (prend en compte le schéma de la table) :

-- Drop default contstraint for SchemaName.TableName.ColumnName
DECLARE @schema_name NVARCHAR(256)
DECLARE @table_name NVARCHAR(256)
DECLARE @col_name NVARCHAR(256)
DECLARE @Command  NVARCHAR(1000)

set @schema_name = N'SchemaName'
set @table_name = N'TableName'
set @col_name = N'ColumnName'

SELECT @Command = 'ALTER TABLE [' + @schema_name + '].[' + @table_name + '] DROP CONSTRAINT ' + d.name
 FROM sys.tables t   
  JOIN sys.default_constraints d       
   ON d.parent_object_id = t.object_id  
  JOIN sys.schemas s
        ON s.schema_id = t.schema_id
  JOIN    sys.columns c      
   ON c.object_id = t.object_id      
    AND c.column_id = d.parent_column_id
 WHERE t.name = @table_name
    AND s.name = @schema_name 
  AND c.name = @col_name

EXECUTE (@Command)