Solved

Dropping a column from a table in SQL database

Posted on 2012-04-07
6
344 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

832 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