How to ALTER the Table Value Parameter

Profile picture for VInayK
VInayK

I am not getting option like 'ALTER TO' when am right clicking on TVP

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