Search code examples
sqlsql-serveralter

How can I DROP COLUMN in Microsoft SQL Server with a value?

Code:

ALTER TABLE tblUser
DROP COLUMN Mobile

Error:

ALTER TABLE DROP COLUMN Mobile failed because one or more objects access this column.

This column had values in Table. How can I delete all objects that access this column?

How can I DROP COLUMN with values?

how can do it with code? How can I delete all constraints in column automatically?

Solution 
  • ALTER TABLE DROP COLUMN Mobile failed because one or more objects access this column.

    Your column won't be deleted. Because one column or multiple columns are getting reference from this column that you want to delete.

    So first, you will have to find in which table your column is being referenced by below query.

    SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'TABLENAME'

    It will show you all constraints of all tables of your current database. You need to find it and remove the constraint. After that your column will be deleted successfully because there is no reference of your column in any table.

    To remove constraint from column - use below query

    alter table tablename
drop constraint constraintid