Solved

SQL Drop Column - ALTER TABLE DROP COLUMN failed because one or more objects access this column

Posted on 2014-11-17
7
4,393 Views
Last Modified: 2014-11-17
Hi

I'm testing some code which, among other things, adds columns to some tables.  When I want to run it again to test it, I keep having to go into Design and delete the columns manually, which is a pain,

I've tried using Drop Column but can't as per my question title.  None of these columns are indexed, so I can only assume that it's because they get used later on in the code.  Part of the code is to create them (which I will have to do when I run it live).  Is there any way around this?

I'm awarding 500 points for a quick and working solution.

Thanks
Sarah
0
Comment
Question by:ScuzzyJo
[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
  • 4
  • 3
7 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 40447773
Check for the constraints , default / foreign  etc  key constraints

if you run the following query, it will list out all the dependencies for the table, then you can go and drop the dependent objects and drop the column

exec sp_depends 'enter your tableName here'
0
 

Author Comment

by:ScuzzyJo
ID: 40447801
Hi Aneesh

I got:

Object does not reference any object, and no objects reference it.

It still won't let me drop the column though.

Thanks
Sarah
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 40447853
Doesn't make sense
 can you try this one

 SELECT
    df.name 'Constraint Name' ,
    t.name 'Table Name',
    c.NAME 'Column Name'
FROM sys.default_constraints df
INNER JOIN sys.tables t ON df.parent_object_id = t.object_id
INNER JOIN sys.columns c ON df.parent_object_id = c.object_id AND df.parent_column_id = c.column_id
WHERE t.name = ''  --- put the table name here
AND c.NAME  = '' -- put the column name here

also please send the exact error
0
Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

 

Author Comment

by:ScuzzyJo
ID: 40447865
Hi Aneesh

This is weird.  That didn't work.  I've put everything below:

My original query was:
ALTER TABLE SAS_T_All_Data DROP COLUMN Concat;
GO

I got the error:
Msg 5074, Level 16, State 1, Line 4
The object 'DF__SAS_T_All__Conca__634EBE90' is dependent on column 'Concat'.
Msg 4922, Level 16, State 9, Line 4
ALTER TABLE DROP COLUMN Concat failed because one or more objects access this column.

I entered yours above as:
SELECT
    df.name 'Constraint Name' ,
    t.name 'Table Name',
    c.NAME 'Column Name'
FROM sys.default_constraints df
INNER JOIN sys.tables t ON df.parent_object_id = t.object_id
INNER JOIN sys.columns c ON df.parent_object_id = c.object_id AND df.parent_column_id = c.column_id
WHERE t.name = SAS_T_All_Data  --- put the table name here
AND c.NAME  = Concat -- put the column name here

and got:
Msg 207, Level 16, State 1, Line 11
Invalid column name 'SAS_T_All_Data'.
Msg 207, Level 16, State 1, Line 12
Invalid column name 'Concat'.
 
I don't understand this last bit as I can see SAS_T_All_Data!

Thanks
Sarah
0
 

Author Comment

by:ScuzzyJo
ID: 40447872
Hi Aneesh

OK, I've put the inverted commas back in now and your query worked.  I got:

Constraint Name
DF__SAS_T_All__Conca__634EBE90

I have no idea what this means :-)

Thanks
Sarah
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 40447901
That's the default constraint, when some one created the table, that column was assigned a default value and hence a constraint was created.
Now for dropping the column you need to drop the constraint first,  then issue the drop column statement. But  before you do that, you need to check the default value you assigned for that column, either thru SQL Server management studio or thru the below query

SELECT
     df.name 'Constraint Name' ,
       df.definition,  -- this gives the default value
     t.name 'Table Name',
     c.NAME 'Column Name'
 FROM sys.default_constraints df
 INNER JOIN sys.tables t ON df.parent_object_id = t.object_id
 INNER JOIN sys.columns c ON df.parent_object_id = c.object_id AND df.parent_column_id = c.column_id
  WHERE t.name = 'SAS_T_All_Data'  --- put the table name here
 AND c.NAME  = 'CONCAT'

here is the query to drop the constraint

ALTER TABLE SAS_T_All_Data DROP CONSTRAINT DF__SAS_T_All__Conca__634EBE90
0
 

Author Comment

by:ScuzzyJo
ID: 40447947
Hi Aneesh

I've solved it with:

ALTER TABLE SAS_T_All_Data DROP CONSTRAINT DF__SAS_T_All__Conca__634EBE90
ALTER TABLE SAS_T_All_Data DROP COLUMN Concat;
GO

I'm still awarding you the points as I wouldn't have found out how to do that without your help.

Thanks
Sarah
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

726 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