?
Solved

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

Posted on 2014-11-17
7
Medium Priority
?
4,708 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

770 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