A big issue when try to Alter user define table type structure TIP #108

 

I hope all of you aware of  User define table type (a table value parameter) which we discussed earlier in TIP #57.

Now recently one interesting incident happened. We are using a user define table type in few stored procedure and due to some business requirement change we need to change /update data type of a particular column from TINYINT to SMALLINT.

Now this change was seems very simple you just need to change a column’s data type  but when you are going to do this, you will find this is not pretty straight forward (if the User define table type is referred in different tables).

If you go through standard steps you need to follow below steps (for  a column data type change)

1) Create a new User define table  type

2) Replaced old User define table  type with new user define table type in each stored procedures

3) Remove old User define table type.

And , I would like to say a big thanks  Mr.Norlado  who post an alternative on stackoverflow

below is the alternative steps

1) Rename the existing table type with following command

EXEC sys.sp_rename 'dbo.StudentTableType', 'zStudentTableType';

2) Create Table type with your  changes which you want

CREATE TYPE dbo.StudentTableType AS TABLE(
    StudentId INT NOT NULL,
    Name VARCHAR(255) NOT NULL,
    ClassId SMALLINT -- changed from tinyInt to smallint
);
3. Update the reference in sql entities

DECLARE @Name NVARCHAR(776);

DECLARE REF_CURSOR CURSOR FOR
SELECT referencing_schema_name + '.' + referencing_entity_name
FROM sys.dm_sql_referencing_entities('dbo.StudentTableType', 'TYPE');

OPEN REF_CURSOR;

FETCH NEXT FROM REF_CURSOR INTO @Name;
WHILE (@@FETCH_STATUS = 0)
BEGIN
    EXEC sys.sp_refreshsqlmodule @name = @Name;
    FETCH NEXT FROM REF_CURSOR INTO @Name;
END;

CLOSE REF_CURSOR;
DEALLOCATE REF_CURSOR;

4. Now drop the renamed table type

DROP TYPE dbo.zStudentTableType;

This 4 steps helped me a lot.

I hope this may help you as well.

Enjoy!!!

RJ!!!