Enforced Dependencies and Schemabinding

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.