Solved

Dropping a column from a table in SQL database

Posted on 2012-04-07
6
340 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
6 Comments
 
LVL 38

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In this article I will describe the Detach & Attach 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.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now