How to ALTER the Table Value Parameter

Question

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

ALTER TO for TVP?? a TVP is part of a function / procedure definition. Not an entity by itself!? — Nestor, Nov 14 '09 at 15:32
Table-valued parameters are a new parameter type in SQL Server 2008. Table-valued parameters are declared by using user-defined table types. I guess you are asking about ALTERing user-defined table type?? As far as I know, you can CREATE and DROP a user-defined table type, but not ALTER it. — Nestor, Nov 14 '09 at 17:25

score 46 · Answer 1 · answered Nov 14 '09 at 19:23

46

Can't do it. You must drop/recreate. If you have dependencies on the TVP, you must:

create new TVP under new name
alter dependencies to use (1)
drop old TVP
recreate (1) under original name
alter dependencies to use (4)
drop (1)

answered Nov 14 '09 at 19:23

Peter Radocchia

10,710
2
34
56

6

Thank you, but the hassle sucks. – Pete Alvin Feb 18 '16 at 20:34
Why do you have to use a new name? – TrevorBrooks Oct 24 '19 at 20:11
You can't have two types with the same name, and you can't drop the old type if it's being referenced by another object. If you don't mind dropping all the referencing objects first, and then dropping the type, then you can recreate it using the same name directly, and not go through this dance. But if you cannot afford to drop referencing objects first, because you cannot afford any downtime, then you must go through this dance. – Peter Radocchia Nov 01 '19 at 17:46

score 16 · Answer 2 · edited Sep 07 '15 at 17:34

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

I just modified it a bit.

1.You should 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 should run it with your table type name as a input parameter. Show the results on grid format (because text format might truncate long texts), select entire result table and copy it to a new query window.

The Row with the Create seems to be missing CR+LF's. When I Pasted it into a new Query Window or a text editor, it Pasted as one big line. I was using SSMS 2016 running on SS 2008 R2. — Tom, Mar 08 '17 at 18:26
See this answer to make SSMS grid results retain CR/LF on copy https://stackoverflow.com/a/37284582/3055415 — Justin Moser, Jun 05 '19 at 12:55
Brilliant tool - CR+LF's. was an issue, but this saved a lot of time — Ben Watson, Jun 27 '19 at 22:48

score 3 · Answer 3 · answered Aug 14 '19 at 22:42

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

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

How to ALTER the Table Value Parameter

4 Answers4

Problem

Find Usages

Steps

Further Reading

Linked