How to drop SQL default constraint without knowing its name

Question

In Microsoft SQL Server  I know the query to check if a default constraint exists for a column and drop a default constraint is   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   But due to typo in previous versions of the database  the name of the constraint could be DF SomeTable ColName or DF SmoeTable ColName   How can I delete the default constraint without any SQL errors  Default constraint names don t show up in INFORMATION SCHEMA table  which makes things a bit trickier   So  something like  delete the default constraint in this table column   or  delete DF SmoeTable ColName   but don t give any errors if it can t find it

User · Answer

declare  table name nvarchar 100  declare  col name nvarchar 100  declare  constraint nvarchar 100  set  table name   N TableName  set  col name   N ColumnName   IF EXISTS  select       c       from        sys columns c      inner join  sys tables t on t object id   c object id     where       t name    table name     and         c name    col name   BEGIN  select  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 c name    col name      IF LEN ISNULL  constraint        lt  gt  0     BEGIN         DECLARE  sqlcmd VARCHAR MAX          SET  sqlcmd    ALTER TABLE     QUOTENAME  table name      DROP CONSTRAINT             QUOTENAME  constraint           EXEC   sqlcmd        END  END GO

User · Answer

Expanded solution  takes table schema into account    -- 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

User · Answer

Expanding on Mitch Wheat s code  the following script will generate the command to drop the constraint and dynamically execute it   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

User · Answer

Drop all default contstraints in a database - safe for nvarchar max  threshold       WARNING  THE SAMPLE BELOW  DROPS ALL THE DEFAULT CONSTRAINTS IN A DATABASE        MAY 03  2013 - BY WISEROOT     declare  table name nvarchar 128  declare  column name nvarchar 128  declare  df name nvarchar 128  declare  cmd nvarchar 128    declare table names cursor for   SELECT t name TableName  c name ColumnName  FROM sys columns c INNER JOIN      sys tables t ON c object id   t object id INNER JOIN      sys schemas s ON t schema id   s schema id      ORDER BY T name  c name       open table names fetch next from table names into  table name    column name while   fetch status   0 BEGIN  if exists  SELECT top 1  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 c name    column name  BEGIN     SET  df name    SELECT top 1  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 c name    column name      select  cmd    ALTER TABLE       table name       DROP CONSTRAINT        df name           print  cmd     EXEC sp executeSQL  cmd  END    fetch next from table names into  table name    column name END  close table names  deallocate table names

User · Answer

Rob Farley s blog post might be of help    Two ways to find   drop a default constraint without knowing its name   Something like    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

User · Answer

To drop constraint for multiple columns    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

User · Answer

Useful for some columns that had multiple default constraints or check constraints created   Modified https   stackoverflow com a 16359095 206730 script       Note  this script is for sys check constraints   declare  table name nvarchar 128  declare  column name nvarchar 128  declare  constraint name nvarchar 128  declare  constraint definition nvarchar 512   declare  df name nvarchar 128  declare  cmd nvarchar 128    PRINT  DROP CONSTRAINT  Roles2016 UsersCRM  Estado   declare constraints cursor for   select t name TableName  c name ColumnName  d name ConstraintName  d definition ConstraintDefinition  from sys tables t     join sys check 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   N Roles2016 UsersCRM  and c name   N Estado   open constraints fetch next from constraints into  table name    column name   constraint name   constraint definition while   fetch status   0 BEGIN     print  CONSTRAINT       constraint name     select  cmd    ALTER TABLE       table name       DROP CONSTRAINT        constraint name           print  cmd     EXEC sp executeSQL  cmd     fetch next from constraints into  table name    column name   constraint name   constraint definition END  close constraints  deallocate constraints

User · Answer

I had some columns that had multiple default constraints created  so I create the following stored procedure   CREATE PROCEDURE  dbo   RemoveDefaultConstraints   table name nvarchar 256    column name nvarchar 256  AS BEGIN      DECLARE  ObjectName NVARCHAR 100       START  --Start of loop     SELECT           ObjectName   OBJECT NAME  default object id        FROM          SYS COLUMNS     WHERE           object id    OBJECT ID  table name           AND  name     column name       -- Don t drop the constraint unless it exists     IF  ObjectName IS NOT NULL     BEGIN         EXEC   ALTER TABLE    table name   DROP CONSTRAINT      ObjectName          GOTO START  --Used to loop in case of multiple default constraints     END END GO  -- How to run the stored proc   This removes the default constraint s  for the enabled column on the User table  EXEC  dbo   RemoveDefaultConstraints  N  dbo   User    N enabled  GO  -- If you hate the proc  just get rid of it DROP PROCEDURE  dbo   RemoveDefaultConstraints  GO

User · Answer

This was the easiest solution that I found   Select Table  Press ALT   F1  Scroll and view constraint names   Then the query is simple  ALTER TABLE  Table  DROP CONSTRAINT  Constraint   https   www sqlservergeeks com sql-server-dropping-a-primary-key-constraint-in-sql-server-is-it-easy-may-be-may-be-not

User · Answer

Always generate script and review before you run  Below the script    select  Alter table dbo     t 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 c name in   VersionEffectiveDate   VersionEndDate   VersionReasonDesc     order by t name

User · Answer

I found that this works and uses no joins   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    Just make sure that columnName does not have brackets around it because the query is looking for an exact match and will return nothing if it is  columnName

User · Answer

declare  ery nvarchar max  declare  tab nvarchar max     myTable  declare  qu nvarchar max     alter table    tab   drop constraint    select  ery    select bj name from sys tables as tb  inner join sys objects as bj  on tb object id   bj parent object id where tb name    tab and bj type    PK    exec  qu  ery    Take a look

User · Answer

Following solution will drop specific default constraint of a column from the table  Declare  Const NVARCHAR 256   SET  Const                   SELECT TOP 1  ALTER TABLE    YOUR TABLE NAME    DROP CONSTRAINT   name               FROM Sys default constraints A               JOIN sysconstraints B on A parent object id   B id               WHERE id   OBJECT ID  YOUR TABLE NAME                 AND COL NAME id  colid   COLUMN NAME                AND OBJECTPROPERTY constid  IsDefaultCnst   1                EXEC   Const

User · Answer

Run this command to browse all constraints   exec sp helpconstraint  mytable  --and look under constraint name     It will look something like this  DF  Mytable  Column   ABC123   Then you can just drop the constraint

User · Answer

I hope this could be helpful for whom has similar problem   In ObjectExplorer window  select your database   Tables    your table   Constraints  If the customer is defined on create column time  you can see the default name of constraint including the column name  then use   ALTER TABLE  yourTableName DROP CONSTRAINT DF  YourTa  NewCo  47127295     the constraint name is just an example

[sql] How to drop SQL default constraint without knowing its name?

Examples related to sql

Examples related to sql-server

Examples related to tsql