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

Question

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?

First check what use it (VIEW WITH SCHEMABINDING, CONSTRAINTS, INDEXES, COMPUTED COLUMNS...) You cannot just drop column when something else depends on it — Lukasz Szozda
– Lukasz Szozda, Commented Sep 13, 2015 at 10:54
See table definition for indexes/computed columns and constraints or check in Object Explorer in SSMS — Lukasz Szozda
– Lukasz Szozda, Commented Sep 13, 2015 at 10:57
Seriously, you do DDL on DB and don't know how to check it? — Lukasz Szozda
– Lukasz Szozda, Commented Sep 13, 2015 at 10:59
how can do it with code? how can Delete all constraints in column Automatic؟ — shahroz
– shahroz, Commented Sep 13, 2015 at 11:10
You need to know what those constraints are and what their names are in order to drop them; there's nothing in SQL Server to say DROP ALL CONSTRAINTS and just do it. — marc_s
– marc_s, Commented Sep 13, 2015 at 11:55

Puneet Chawla · Accepted Answer · 2015-09-15 04:45:37Z

1

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

answered Sep 15, 2015 at 4:45

Puneet Chawla

6,0795 gold badges18 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

M_Idrees · Accepted Answer · 2015-09-13 14:09:34Z

1

SQL Search is a great tool. I will search for your all the objects which are using the targeted object.

You can easily find where your column is being used, then simply you can modify or drop that objects too.

answered Sep 13, 2015 at 14:09

M_Idrees

2,1803 gold badges28 silver badges55 bronze badges

Comments

Indra Prakash Tiwari · Accepted Answer · 2015-09-15 04:39:01Z

1

Use below query to find the constraints name for particular tablename

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

Noe you can see the constraints name under constraint_name column, drop all constraint using below syntax

  ALTER TABLE TABLENAME DROP CONSTRAINT CONSTRATINTSNAME

After that you can use below statement to drop the column

ALTER TABLE TABLENAME DROP COLUMN COLUMNNAME

answered Sep 15, 2015 at 4:39

Indra Prakash Tiwari

1,0671 gold badge7 silver badges16 bronze badges

Comments

shahroz · Accepted Answer · 2015-09-15 04:30:40Z

0

You need to know what those constraints are and what their names are in order to drop them; there's nothing in SQL Server to say DROP ALL CONSTRAINTS and just do it. – marc_s yesterday

answered Sep 15, 2015 at 4:30

shahroz

3691 gold badge6 silver badges18 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related