In trying to rename a table I encountered the dreaded error about enforced dependencies:
Object
'myTable' cannot be renamed because the object participates in enforced
dependencies.
I first used the following script to identify the objects dependent upon the table:
SELECT DISTINCT o.object_id, o.name, o.type_desc, m.definition
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
JOIN sys.sql_modules m ON m.object_id = o.object_id
WHERE d.class = 1
AND r.name = 'myTable'
This yielded the names of three objects in my database which happen to use SCHEMABINDING in their definitions.
I didn't want to manually alter and recreate these objects, so instead I decided to create a script which will find the SQL code for each object, remove the WITH SCHEMABINDING syntax, drop the existing objects and then recreate them sans schema binding.
DECLARE
@TheTable varchar(100),
@Count int,
@Max int,
@object_id int,
@name varchar(50),
@type_desc varchar(50),
@definition nvarchar(max),
@DropSQL nvarchar(max),
@CreateSQL nvarchar(max)
-- Remove schemabinding from objects tied to table.
DECLARE @Objects TABLE
(
ID int IDENTITY (1, 1) PRIMARY KEY NOT NULL,
object_id int,
name varchar(50),
type_desc varchar(50),
definition nvarchar(max)
)
-- Set the table name here:
SET @TheTable = 'MyTable'
INSERT INTO @Objects (object_id, name, type_desc, definition)
SELECT DISTINCT o.object_id, o.name, o.type_desc, m.definition
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
JOIN sys.sql_modules m ON m.object_id = o.object_id
WHERE d.class = 1
AND r.name = @TheTable
UPDATE @Objects
SET definition =
CASE
WHEN definition LIKE '%SCHEMABINDING%' THEN REPLACE(definition, 'WITH SCHEMABINDING', '')
ELSE definition
END
SELECT @Count = 1, @Max = COUNT(ID) FROM @Objects
WHILE @Count <= @Max
BEGIN
SELECT @object_id = object_id, @name = name, @type_desc = type_desc, @definition = definition
FROM @Objects
WHERE ID = @Count
SET @DropSQL = 'DROP '
+
CASE @type_desc
WHEN 'VIEW' THEN 'VIEW'
WHEN 'SQL_SCALAR_FUNCTION' THEN 'FUNCTION'
WHEN 'SQL_TABLE_VALUED_FUNCTION' THEN 'FUNCTION'
WHEN 'SQL_STORED_PROCEDURE' THEN 'PROCEDURE'
WHEN 'SQL_TRIGGER' THEN 'TRIGGER'
END
+
' '
+
@name
PRINT @DropSQL
EXEC sp_executeSQL @DropSQL
SET @CreateSQL = @definition
PRINT @CreateSQL
EXEC sp_executeSQL @CreateSQL
SET @Count = @Count + 1
END
Now I can incorporate this logic into a sproc and not have to worry about meddlesome constraints if I'm going to be renaming tables. The above script has been tested successfully under SQL 2008 and 2012, and will be able to automatically drop and recreate views, scalar and table valued functions, sprocs and triggers.
