Solved

Dropping a column from a table in SQL database

Posted on 2012-04-07
6
356 Views
Last Modified: 2012-04-07
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
Comment
Question by:adamtrask
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 37819780
Did you create an index on this column?
0
 
LVL 59

Expert Comment

by:Darius Ghassem
ID: 37819847
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
 
LVL 14

Expert Comment

by:nishant joshi
ID: 37819872
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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

Author Comment

by:adamtrask
ID: 37819898
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
 
LVL 17

Accepted Solution

by:
Anuroopsundd earned 500 total points
ID: 37820007
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
 

Author Closing Comment

by:adamtrask
ID: 37820124
thank you
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question