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!!!
