Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Dropping a column from a table in SQL database

Posted on 2012-04-07
6
Medium Priority
?
358 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 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

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 2000 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

877 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