Hi @rajesh yadav ,
Welcome to Microsoft Q&A!
You could refer Guoxiong's article and follow the steps one by one.
I found another method in this forum and you could also have a try.
One note is the sp_refreshsqlmodule will fail if the change you made to the table type is a breaking change to that object, typically a procedure.
- Use sp_rename to rename the table type, I typically just add z to the beginning of the name.
- Create a new table type with the original name and any modification you need to make to the table type.
- Step through each dependency and run sp_refreshsqlmodule on it.
- Drop the renamed table type.
I also had a try from my side as below:
CREATE TYPE [dbo].[udttR] AS TABLE(
[ProjectId] [bigint] NULL
)
CREATE PROCEDURE dbo.MyProcedure1
@tvp dbo.[udttR] READONLY
AS
BEGIN
SELECT [ProjectId] FROM @tvp;
END
GO
CREATE PROCEDURE dbo.MyProcedure2
AS
BEGIN
DECLARE @tvp dbo.[udttR];
END
GO
EXEC sys.sp_rename 'dbo.udttR', 'zudttR';
CREATE TYPE [dbo].[udttR] AS TABLE(
[ProjectId] [int] NULL
)
DECLARE @Name NVARCHAR(776);
DECLARE REF_CURSOR CURSOR FOR
SELECT referencing_schema_name + '.' + referencing_entity_name
FROM sys.dm_sql_referencing_entities('dbo.udttR', '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;
GO
DROP TYPE dbo.zudttR;
GO
After of all, before it could be destructive to your database, you should test this on a development environment firstly.
Best regards
Melissa
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.