SQL Server 2012 - Cannot drop User defined (table) type while there are no active dependencies

Question

I have a User-defined type within which the size of one VARCHAR column needs to be increased. Altering the type is not possible because of dependencies (Stored procedures using that type). So, I tried the following:

This is the scenario:

1. Rename the type from T_MyType to T_MyType_1,
2. Ask Management Studio to build a creation script for type T_MyType_1,
3. Edit the script, remove the "_1" from the type name and increase size of one field, then run the script,
4. Run the procedure that caused the truncation problem (column length) and got a successful result (no truncation nor error messages),
5. Attempt to delete type T_MyType_1 and got error stating that the type is being used by procedure ABC,
6. Search for T_MyType_1 within the body of procedure ABC and string not found.

So, I'm stuck with a type defined that needs to be deleted.

Edit

I believe this question is different to the proposed duplicates.

Answer 1

Renaming is a dangerous pastime because it doesn't mesh well with SQL Server's limited implementation of dependency tracking, and the deferred name resolution of stored procedures. I couldn't actually reproduce this specific scenario on SQL Server 2017 (dependencies were correctly tracked if the UDT is used in the procedure's body) so it's possible this case has been improved. Certainly, if you use the UDT as a parameter it actually detects the problem and gives an appropriate error message:

CREATE TYPE BadUdt FROM NVARCHAR(10);
GO
CREATE PROCEDURE AreUdtsBadThough(@T BadUdt) AS BEGIN
    RETURN;
END;
GO
EXEC sp_rename 'BadUdt', 'GoodUdt';
GO
CREATE TYPE BadUdt FROM NVARCHAR(20);
GO
EXEC AreUdtsBadThough NULL

Msg 496, Level 16, State 1, Procedure AreUdtsBadThough, Line 6 [Batch Start Line 16] The parameter "@T" is not the same type as the type it was created with. Drop and recreate the module using a two-part name for the type, or use sp_refreshsqlmodule to refresh its parameters metadata.

Despite the name change, the parameter of AreUdtsBadThough is still linked to the old type by ID, which is why you can't drop that:

SELECT p.[name], t.[name]
FROM sys.parameters p
JOIN sys.types t ON p.user_type_id = t.user_type_id
WHERE [object_id] = OBJECT_ID('AreUdtsBadThough')

+------+---------+
| name |  name   |
+------+---------+
| @T   | GoodUdt |
+------+---------+

The error helpfully describes the general solution for dependency problems: invoke sp_refreshsqlmodule, which forces the stored procedure (or function, or trigger, or view) to effectively be recompiled and the dependencies to be updated:

EXEC sp_refreshsqlmodule 'AreUdtsBadThough'

SELECT p.[name], t.[name]
FROM sys.parameters p
JOIN sys.types t ON p.user_type_id = t.user_type_id
WHERE [object_id] = OBJECT_ID('AreUdtsBadThough')

+------+--------+
| name |  name  |
+------+--------+
| @T   | BadUdt |
+------+--------+

And now GoodUdt has no dependencies anymore and can be dropped.

The problem of dependencies not always getting updated correctly (which is not restricted to renaming) has prompted some people to come up with more permanent solutions. Aaron Bertrand has an article on automating the dependency refreshes for SQL Server 2008 (which should still work for later editions, even if it may do more work than necessary) due to improved dependency checks.