views:

504

answers:

2

How do you force a rename???

Rename failed for Table 'dbo.x. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.2531.0+%28%28Katmai%5FPCU%5FMain%29.090329-1045+%29&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Rename+Table&LinkId=20476


An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)


Object '[dbo].[x]' cannot be renamed because the object participates in enforced dependencies. (Microsoft SQL Server, Error: 15336)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.4035&EvtSrc=MSSQLServer&EvtID=15336&LinkId=20476

+2  A: 

Find the "enforced dependencies", then remove or disable them.

By "enforced dependencies", it means Schema binding, so you'll have to look specifically for that.

Here's a query to look for schema binding references to your object:

select o.name as ObjName, r.name as ReferencedObj
from sys.sql_dependencies d
join sys.objects o on o.object_id=d.object_id
join sys.objects r on r.object_id=d.referenced_major_id
where d.class=1
AND r.name = @YourObjectName

As I noted in the comments, there is no way to FORCE-ibly override Schema Binding. When you use Schema Binding, you are explicitly saying "Do not let me or anyone else override this." The only way around Schema Binding is to undo it, and that's intentional.

RBarryYoung
note: *FORCE*, curious if it can be done
Scott Kramer
Note my answer. It is correct, and the only way to do it. When you use schema bindig, you are explicitly forfeiting any way to "FORCE" it. You must first undo the schema binding
RBarryYoung
@Scott: I guess RBarryYoung is saying "no: you can not force this"
gbn
Heh. Yes, that captures it. ;-)
RBarryYoung
A: 

You may not be able to rename the object without first removing its dependencies (e.g., deleting FK constraints and recreating them afterwards). You can either do this manually, or use a tool like SQL Refactor from the Redgate SQL Toolbelt:

http://www.red-gate.com/products/SQL_Professional_Toolbelt/index.htm

I recommend these tools, they are extremely useful; however, they are also pretty expensive. You can do it using the free trial version, though. There is also a tool in this tools suite which will help you track down the object's dependencies (SQL Dependency Tracker).

RMorrisey
nope, i just need to do this quickly... its just a bunch of views, I don't want to drop... note *FORCE*
Scott Kramer