Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
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
?
5,594 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
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.
Suggested Courses

581 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