How to ALTER the Table Value Parameter

Problem

You cannot drop the User Defined Table Type as long as it's referenced by anything else:

Cannot drop type 'dbo.MyTableType' because it is being referenced by object 'MyStoredProcedure'. There may be other objects that reference this type.

It would be nice if SSMS gave you a listing of all other objects, but if you don't have many, a partially manual approach might work fine.

Find Usages

To get a list of all SPs that use your TVP type, you can query sys.sql_expression_dependencies

SELECT OBJECT_NAME(d.referencing_id)
FROM sys.sql_expression_dependencies d
WHERE d.referenced_id = TYPE_ID('MyTableType')

Steps

1. Select all SPs identified above and select DROP and CREATE to new window
2. Go through each window, and just highlight / execute the DROP PROCEDURE section
3. Now you can select your type and select DROP and CREATE to new window and make any changes
4. Go back to the list of SP's windows you opened and execute the CREATE section

Further Reading

• Altering user-defined table types in SQL Server
• How to do ALTER TABLE TYPE efficiently
• List user defined types in a SQL Server database

1. Drop all the SP's which uses the TVP you want to alter.(Don't Forget to get the backup the of SP's & TVP)
2. Now Drop the TVP Which you have to alter.
3. Than Modify the same TVP which you have dropped in step 2 & than Create it Again.(TVP is altered here).
4. Now Create again all the SP's Which you have dropped in 1st Step. (Your SP's are ready to use with altered TVP).

You will get back the TVP and All the Sp's which uses the same TVP.