alter table failed

[swoodring]

I have a table in which I am expanding about 10 fields. I'm issuing the alter table commnand individually.

I have expanded 7 of them suscessfully but the other 3 I'm getting an error.

This is the command I'm using:
Alter Table WeeklysalesHistory Alter column costofsales decimal(11,0)

It comes back with the following errors:
Server: Msg 5074, Level 16, State 8, Line 1
The statistics 'hind_113_6_1_2_3_4_5_7_9_10_14_19' is dependent on column 'costofsales'.

It lists this error over and over and then has this error as well.

Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN costofsales failed because one or more objects access this column.

Any ideas on what is causing this. There is not an index on this field.

Thanks. Stacy

 

[MeanGreen]

Looks to me like a different table has a reference to this tables column. In Enterprise manager use the database Diagram and add that table with references to try and find the relation.

Good Luck.

 

[swoodring]

I have a diagram with this weeklysaleshistory and have done show dependencies and its not coming back with anything.

I do have an idenity column on this table. Could this be causing the problem?

Stacy

 

[MeanGreen]

Try this in Query Analyzer:

sp_depends <tablename>


Then use that list to see if you can figure out what is referencing that column.

Good Luck.

 

[swoodring]

I did the following query in enterprise manager:
sp_depends weeklysaleshistory

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

Which is what I would expect. I don't recall this object referencing anything else. Any other ideas?

Stacy

 

[MeanGreen]

Again, you have something set up on the table. It looks like you have a statistic set up on the table. To be honest, I haven't done much with statistics, but I think you can run sp_helpindex <table> and it will list any indexes/statistics on the table. You might try DBCC CheckTable <table> and see if it reports anything.

Good Luck.

 

[MeanGreen]

There is also sp_statistics <table>. Just another thought.

Good Luck.

 

[swoodring]

yep I just found some statistics set up on these columns that probally got created with the auto update statstics options. I'm going to try dropping the statistics using drop stastics and then try altering the table after that. Thanks for your help.

Stacy