Why sp_rename don't work for CHECK constraint?

Question

To rename the CHECK I use query like this:

 EXEC sp_rename 'test_create_table.test_check.check_1', 'check1', 'OBJECT'

Query executes without error but the name of constraint not changed.

Some one knows why?

Answer 1

You need to pay special heed to the warning on the page of documentation:

If the object to be renamed is a constraint, object_name must be in the form schema.constraint.

Which I don't think the name you're giving is. So, if this is a constraint on a table called test_create_table in the foobar schema, what you actually need is:

EXEC sp_rename 'foobar.check_1', 'check1', 'OBJECT'

I don't know why it doesn't give any form of feedback when you try it the other way, but you'll note that you don't receive this message either:

Caution: Changing any part of an object name could break scripts and stored procedures.

Which is the usual output when sp_rename is successful.

Answer 2

This should successfully rename your table. I modified your command a bit.

EXEC sp_rename 'test_create_table.test_check', 'check1';

Just verifying what I'm seeing - test_create_table is the name of your schema, test_check is the name of your table, and you are remaing the table to check1?

Also, it looks like you are including an object. Make sure you have created your obect: CREATE OBJECT YourObjectName ON include your table information here.

This link might also help: http://technet.microsoft.com/en-us/library/ms188351.aspx

The example with the object code is at the bottom.