How to drop column with constraint

Question

How to drop a column which is having Default constraint in SQL Server 2008   My query is   alter table tbloffers drop column checkin   I am getting below error     ALTER TABLE DROP COLUMN checkin failed because one or more objects access this column    Can anyone correct my query to drop a column with constraint

User · Answer

I have updated script a little bit to my SQL server version  DECLARE  sql nvarchar max   SELECT  sql    ALTER TABLE  table name  DROP CONSTRAINT     df NAME  FROM sys default constraints df   INNER JOIN sys tables t ON df parent object id   t object id   INNER JOIN sys columns c ON df parent object id   c object id AND df parent column id   c column id where t name    table name  and c name    column name   EXEC sp executeSql  sql GO  ALTER TABLE table name   DROP COLUMN column name

User · Answer

It s not always just a default constraint that prevents from droping a column and sometimes indexes can also block you from droping the constraint  So I wrote a procedure that drops any index or constraint on a column and the column it self at the end    IF OBJECT ID   ADM delete column    P   IS NOT NULL    DROP procedure ADM delete column  GO  CREATE procedure ADM delete column      table name in  nvarchar 300       column name in nvarchar 300  AS  BEGIN         Author  Matthis  matthis online ms at 2019 07 20          License CC BY  creativecommons org          Desc    Administrative procedure that drops columns at MS SQL Server                 - if there is an index or constraint on the column                      that will be dropped in advice                   gt  input parameters are TABLE NAME and COLUMN NAME as STRING            SET NOCOUNT ON      --drop index if exist  search first if there is a index on the column      declare  idx name VARCHAR 100      SELECT  top 1  idx name   i name     from    sys tables t     join    sys columns c     on      t object id   c object id     join    sys index columns ic     on      c object id   ic object id     and     c column id   ic column id     join    sys indexes i     on      i object id   ic object id     and     i index id   ic index id     where   t name like  table name in     and     c name like  column name in     if       idx name is not null     begin          print concat  DROP INDEX     idx name    ON     table name in          exec   DROP INDEX      idx name     ON      table name in      end      --drop fk constraint if exist  search first if there is a constraint on the column      declare  fk name VARCHAR 100      SELECT  top 1  fk name   CONSTRAINT NAME      from    INFORMATION SCHEMA CONSTRAINT COLUMN USAGE     where   TABLE NAME like  table name in     and     COLUMN NAME like  column name in     if       fk name is not null     begin          print concat  ALTER TABLE     table name in    DROP CONSTRAINT     fk name          exec   ALTER TABLE      table name in     DROP CONSTRAINT      fk name      end      --drop column if exist     declare  column name VARCHAR 100      SELECT  top 1  column name   COLUMN NAME      FROM    INFORMATION SCHEMA COLUMNS      WHERE   COLUMN NAME like concat      column name in          if   column name is not null     begin          print concat  ALTER TABLE     table name in    DROP COLUMN     column name          exec   ALTER TABLE      table name in     DROP COLUMN      column name      end end  GO   --to run the procedure use this execute and fill the parameters  execute ADM delete column       table name in            column name in

User · Answer

Here s another way to drop a default constraint with an unknown name without having to first run a separate query to get the constraint name   DECLARE  ConstraintName nvarchar 200  SELECT  ConstraintName   Name FROM SYS DEFAULT CONSTRAINTS WHERE PARENT OBJECT ID   OBJECT ID    TableName     AND PARENT COLUMN ID    SELECT column id FROM sys columns                         WHERE NAME   N   ColumnName                            AND object id   OBJECT ID N   TableName      IF  ConstraintName IS NOT NULL EXEC  ALTER TABLE   TableName   DROP CONSTRAINT      ConstraintName

User · Answer

Find the default constraint with this query here   SELECT     df name  Constraint Name        t name  Table Name       c NAME  Column Name  FROM sys default constraints df INNER JOIN sys tables t ON df parent object id   t object id INNER JOIN sys columns c ON df parent object id   c object id AND df parent column id   c column id   This gives you the name of the default constraint  as well as the table and column name   When you have that information you need to first drop the default constraint   ALTER TABLE dbo YourTable DROP CONSTRAINT name-of-the-default-constraint-here   and then you can drop the column  ALTER TABLE dbo YourTable DROP COLUMN YourColumn

User · Answer

First you should drop the problematic DEFAULT constraint  after that you can drop the column  alter table tbloffers drop constraint  ConstraintName  go  alter table tbloffers drop column checkin   But the error may appear from other reasons - for example the user defined function or view with SCHEMABINDING option set for them   UPD  Completely automated dropping of constraints script   DECLARE  sql NVARCHAR MAX  WHILE 1 1 BEGIN     SELECT TOP 1  sql   N alter table tbloffers drop constraint    dc NAME N        from sys default constraints dc     JOIN sys columns c         ON c default object id   dc object id     WHERE          dc parent object id   OBJECT ID  tbloffers       AND c name   N checkin      IF   ROWCOUNT   0 BREAK     EXEC   sql  END

User · Answer

I got the same    ALTER TABLE DROP COLUMN failed because one or more objects access this column message   My column had an index which needed to be deleted first  Using sys indexes did the trick    DECLARE  sql VARCHAR max   SELECT  sql    DROP INDEX     idx NAME     ON tblName  FROM sys indexes idx INNER JOIN sys tables tbl ON idx object id   tbl object id INNER JOIN sys index columns idxCol ON idx index id   idxCol index id INNER JOIN sys columns col ON idxCol column id   col column id WHERE idx type  lt  gt  0     AND tbl NAME    tblName      AND col NAME    colName   EXEC sp executeSql  sql GO  ALTER TABLE tblName DROP COLUMN colName

User · Answer

You can also drop the column and its constraint s  in a single statement rather than individually   CREATE TABLE  T          Col1 INT CONSTRAINT UQ UNIQUE CONSTRAINT CK CHECK  Col1  gt  5        Col2 INT      ALTER TABLE  T DROP CONSTRAINT UQ                        CONSTRAINT CK                       COLUMN Col1   DROP TABLE  T    Some dynamic SQL that will look up the names of dependent check constraints and default constraints and drop them along with the column is below   but not other possible column dependencies such as foreign keys  unique and primary key constraints  computed columns  indexes   CREATE TABLE  dbo   TestTable    A INT DEFAULT  1  CHECK  A 1   B INT  CHECK  A  gt  B     GO  DECLARE  TwoPartTableNameQuoted nvarchar 500      dbo   TestTable             ColumnNameUnQuoted sysname    A            DynSQL NVARCHAR MAX    SELECT  DynSQL         ALTER TABLE      TwoPartTableNameQuoted     DROP           ISNULL   CONSTRAINT     QUOTENAME OBJECT NAME c default object id                      ISNULL check constraints                 COLUMN     QUOTENAME  ColumnNameUnQuoted  FROM   sys columns c        CROSS APPLY  SELECT   CONSTRAINT     QUOTENAME OBJECT NAME referencing id                             FROM   sys sql expression dependencies                     WHERE  referenced id   c object id                            AND referenced minor id   c column id                            AND OBJECTPROPERTYEX referencing id   BaseType      C                      FOR XML PATH      ck check constraints  WHERE  c object id   object id  TwoPartTableNameQuoted         AND c name    ColumnNameUnQuoted   PRINT  DynSQL  EXEC   DynSQL

User · Answer

The following worked for me against a SQL Azure backend  using SQL Server Management Studio   so YMMV  but  if it works for you  it s waaaaay simpler than the other solutions   ALTER TABLE MyTable     DROP CONSTRAINT FK MyColumn     CONSTRAINT DK MyColumn     -- etc        COLUMN MyColumn GO

[sql-server] How to drop column with constraint?

Examples related to sql-server

Examples related to sql-server-2008