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

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
ScuzzyJoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
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
ScuzzyJoAuthor Commented:
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
Aneesh RetnakaranDatabase AdministratorCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ScuzzyJoAuthor Commented:
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
ScuzzyJoAuthor Commented:
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
Aneesh RetnakaranDatabase AdministratorCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ScuzzyJoAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.