Solved

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

Posted on 2014-11-17
7
3,602 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
  • 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-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
This query failed in sql 2014 5 30
Get the latest status 8 31
VB.net and sql server 4 35
Have a conversion issue with varchar to int in a SQL: Query. 1 29
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

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