CODEWITHSUNDEEP
sqlsql-serveralter
shahroz
shahroz

Reputation: 369

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?

Upvotes: 0

Views: 4506

Answers (4)

Puneet Chawla
Puneet Chawla

Reputation: 6009

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

Upvotes: 1

Indra Prakash Tiwari
Indra Prakash Tiwari

Reputation: 1057

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

Upvotes: 1

shahroz
shahroz

Reputation: 369

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

Upvotes: 0

M_Idrees
M_Idrees

Reputation: 2172

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.

Upvotes: 1

Related Questions