How do you force a rename???
Rename failed for Table 'dbo.x. (Microsoft.SqlServer.Smo)
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
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.
Try this:
/*
Example 1: Rename a table dbo.MyTable -> dbo.YourTable
EXEC dbo.USP_DROP_ENFORCED_DEPENDENCIES @SchemaName=N'dbo', @EntityName=N'MyTable', @Debug=1;
EXEC sp_rename N'dbo.MyTable', N'YourTable', N'OBJECT'
Example 2: Rename a column dbo.MyTable.MyColumn -> dbo.MyTable.YourColumn
EXEC dbo.USP_DROP_ENFORCED_DEPENDENCIES @SchemaName=N'dbo', @EntityName=N'MyTable', @ColumnName=N'MyColumn' @Debug=1;
EXEC sp_rename N'dbo.MyTable.MyColumn', N'YourColumn', N'COLUMN'
*/
CREATE Procedure dbo.USP_DROP_ENFORCED_DEPENDENCIES
(
@SchemaName sysname = 'dbo',
@EntityName sysname,
@ColumnName sysname = NULL,
@Debug bit = 0
)
AS
BEGIN
SET NOCOUNT ON;
SET ROWCOUNT 0;
DECLARE @ReferencingEntitySchema sysname, @ReferencingEntityName sysname, @ReferencingEntityType nvarchar(8), @SqlScript nvarchar(512);
DECLARE ReferencingEntitiesCursor CURSOR LOCAL FORWARD_ONLY
FOR
SELECT OBJECT_SCHEMA_NAME(dep.referencing_id) AS [schema]
,referencing_entity.name
,CASE referencing_entity.type
WHEN 'V' THEN N'VIEW'
ELSE /*IF, FN, TF*/ N'FUNCTION'
END as [type]
FROM sys.sql_expression_dependencies AS dep
INNER JOIN sys.objects AS referencing_entity
ON dep.referencing_id = referencing_entity.object_id
WHERE dep.referenced_entity_name = @EntityName
AND dep.referenced_schema_name = @SchemaName
AND is_schema_bound_reference = 1
AND ((@ColumnName IS NULL AND dep.referenced_minor_id = 0) OR COL_NAME(dep.referenced_id, dep.referenced_minor_id) = @ColumnName)
OPEN ReferencingEntitiesCursor
FETCH NEXT FROM ReferencingEntitiesCursor
INTO @ReferencingEntitySchema, @ReferencingEntityName, @ReferencingEntityType;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC dbo.USP_DROP_ENFORCED_DEPENDENCIES @SchemaName=@ReferencingEntitySchema, @EntityName=@ReferencingEntityName, @Debug=@Debug;
--The goal is to produce the following script:
/*
DROP FUNCTION dbo.UFN_SOME_FUNCTION;
OR
DROP VIEW dbo.UFN_SOME_VIEW;
*/
SET @SqlScript = N'DROP ' + @ReferencingEntityType + N' ' + @ReferencingEntitySchema + '.' + @ReferencingEntityName;
IF(@Debug = 1)
RAISERROR (@SqlScript, 0/*severity*/, 0/*state*/) WITH NOWAIT;
EXEC (@SqlScript);
FETCH NEXT FROM ReferencingEntitiesCursor
INTO @ReferencingEntitySchema, @ReferencingEntityName, @ReferencingEntityType;
END
CLOSE ReferencingEntitiesCursor;
DEALLOCATE ReferencingEntitiesCursor;
END
GO
In the SQL Server Object Browser, right-click on the table with the issue and select View Dependencies. Next in the view listed, Right-click (view) and select SCRIPT to CREATE VIEW in New SQL Query Editor window, then remove WITH SCHEMABINDING from the CREATE VIEW t-sql script and run the revised CREATE VIEW t-sql. This unlinks the schema dependency from the table. I was able to recreate the table at this point (DROP, RENAME, etc).
Note:
Schema binding can occur on functions and other objects in your db. Use of
View Dependencieson the object throwing the error is essential to fix the issue.BTW:
I originally added schema binding to enable view indexing. Keeping a good index on the underlying table(s) may mitigate the performance hit of not having one on the view.
