Trying to Rename a Table - Get 'The object cannot be renamed'

[JohnBates]

hi,

I'm trying to rename a SQL Server 2000 user table.
Just using the right-click --> Rename in Enterprise Manager.

It says "The object cannot be renamed"

This database servers data to a website.

Could this message occur because the table is in-use ?

I tried stopping,starting SQL Server Agent but still got the message.

Thanks, John

 

[JohnBates]

more info...
I tried to use sp_rename in Query Analyzer and got this:

Server: Msg 15336, Level 16, State 1, Procedure sp_rename, Line 383
Object '[Fund Transactions]' cannot be renamed because the object participates in enforced dependencies.

 

[JohnBates]

ok I found the cause.

Long, long ago I created an indexed view which included the 'WITH SCHEMABINDING' clause in the create. Really I did this as an experiment, then forgot about it.

Just now, I removed the 'WITH SCHEMABINDING' and recreated the view.

Then I was able to rename the table.

Tip: I ran the sp_help on this table name - that gave me the clue that this view was the culprit.

Darnit, my fault again !
John

 

[sqlturbo]

The second message tells me that 'Fund Transaction' has a relationship with another table in your database. You will have to delete the relationsip first and then delete the table. But if you do that, you would destroy any referential integrity your tables may have and you may end up with orphan or bad data.