• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 360
  • Last Modified:

Dropping a column from a table in SQL database

Hi,

I use the following code to drop a column from a table named firstTable:

alter table FirstTable
drop column [firstTable.desc]

I get the following error:
ALTER TABLE DROP COLUMN firstTable.desc failed because one or more objects access this column.

The table is not related to any other table, I just created it for practice. So, I don't understand what objects access the collumn!
0
adamtrask
Asked:
adamtrask
1 Solution
 
Aaron TomoskySD-WAN SimplifiedCommented:
Did you create an index on this column?
0
 
Darius GhassemCommented:
What type of column is this? Make sure there are no constants on this column.

Is there anymore errors listed? I believe it should tell you what is dependent on this column
0
 
nishant joshiTechnology Development ConsultantCommented:
might some constraint is on table so delete it first after that delete column.
And of it does not works then plese give exact error description.

Thanks.
Nishant
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
adamtraskAuthor Commented:
The column is of type varchar(100). I don't believe there any constraints. When I first created it, I added a default text. Other than that, there are no constrains
0
 
AnuroopsunddCommented:
Here is a script that will delete the column along with its default constraint. Replace MYTABLENAME and MYCOLUMNNAME appropriately.

declare @default sysname, @sql nvarchar(max)  select @default = name  from sys.default_constraints  where parent_object_id = object_id('MYTABLENAME') AND type = 'D' AND parent_column_id = (     select column_id      from sys.columns      where object_id = object_id('MYTABLENAME')     and name = 'MYCOLUMNNAME'     )  set @sql = N'alter table MYTABLENAME drop constraint ' + @default exec sp_executesql @sql  alter table MYTABLENAME drop column MYCOLUMNNAME  go  


http://stackoverflow.com/questions/314998/sql-server-2005-drop-column-with-constraints
0
 
adamtraskAuthor Commented:
thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now