How to change table value parameter

Question

I don’t get an option like "ALTER TO" when I right-click on TVP

Answer 1

Impossible to do this. You have to quit / recreate. If you have dependencies on TVP, you should:

• create a new TVP under a new name
• change dependencies to use (1)
• remove old tvp
• recreate (1) under the original name
• change dependencies to use (4)
• drop (1)

Answer 2

I found a blog post on sqltreeo.com that has a way to automate the process by temporarily dropping dependencies and then re-creating them.

I changed it a bit.

1.You must create the following procedure:

-- Find all referencing objects to user-defined table type in @fullObjectName parameter -- and generate DROP scripts and CREATE scripts for them CREATE PROC [dbo].[alterTableType] (@fullObjectName VARCHAR(200)) AS BEGIN SET NOCOUNT ON IF (TYPE_ID (@fullObjectName) IS NULL) BEGIN RAISERROR ('User-defined table type ''%s'' does not exists. Include full object name with schema.', 16,1, @fullObjectName) RETURN END; WITH sources AS ( SELECT ROW_NUMBER() OVER (ORDER BY OBJECT_NAME(m.object_id)) RowId, definition FROM sys.sql_expression_dependencies d JOIN sys.sql_modules m ON m.object_id = d.referencing_id JOIN sys.objects o ON o.object_id = m.object_id WHERE referenced_id = TYPE_ID(@fullObjectName) ) SELECT 'BEGIN TRANSACTION' UNION ALL SELECT 'DROP ' + CASE OBJECTPROPERTY(referencing_id, 'IsProcedure') WHEN 1 THEN 'PROC ' ELSE CASE WHEN OBJECTPROPERTY(referencing_id, 'IsScalarFunction') = 1 OR OBJECTPROPERTY(referencing_id, 'IsTableFunction') = 1 OR OBJECTPROPERTY(referencing_id, 'IsInlineFunction') = 1 THEN 'FUNCTION ' ELSE '' END END + SCHEMA_NAME(o.schema_id) + '.' + + OBJECT_NAME(m.object_id) FROM sys.sql_expression_dependencies d JOIN sys.sql_modules m ON m.object_id = d.referencing_id JOIN sys.objects o ON o.object_id = m.object_id WHERE referenced_id = TYPE_ID(@fullObjectName) UNION ALL SELECT 'GO' UNION ALL SELECT CHAR(13) + CHAR(10) + '---- WRITE HERE SCRIPT TO DROP OLD USER DEFINED TABLE TYPE AND CREATE A NEW ONE ----' + CHAR(13) + CHAR(10) UNION ALL SELECT CASE WHEN number = RowId THEN DEFINITION ELSE 'GO' END FROM sources s JOIN (SELECT DISTINCT number FROM master.dbo.spt_values) n ON n.number BETWEEN RowId AND RowId+1 UNION ALL SELECT 'COMMIT' END 

2. Then you must run it with the table type name as an input parameter. Show the results in a grid format (since the text format can cut long texts), select the entire result table and copy it into a new query window.

Answer 3

Problem

You cannot delete a custom table type if anything else refers to it:

Cannot delete type "dbo.MyTableType" because the object "MyStoredProcedure" refers to it. There may be other objects that reference this type.

It would be nice if SSMS provided you with a list of all the other objects, but if you don't have them, a partially manual approach might work.

Find use

To get a list of all the 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 the SPs above and select DROP and CREATE to new window
2. Browse through each window and simply select / execute the DROP PROCEDURE section
3. Now you can choose your type and select DROP and CREATE to new window and make any changes.
4. Go back to the list of open SP windows and do the CREATE section.

Further reading

• Changing custom table types in SQL Server
• How to use ALTER TABLE TYPE effectively
• List of user types in a SQL Server database