如何異動使用者資料表值參數類型(User-defined Table Types)的Schema

2021-03-18

1842
0
[SQL]資料維護
2021-03-19

今天收到一個需求得修改資料表值參數類型的欄位長度，但使用者定義資料表值參數類型無法使用Alter來異動，只能Drop後Create來異動。一旦該Table Type有被Stored Procedure參照的話，我們還無法Drop掉…….

上網爬了一下最好的做法就是建立新的Table Type後再做rename動作，最後再用sys.sp_refreshsqlmodule關聯Stored Procedure跟Table Type回來。

步驟一先將舊的Table Type給Rename掉。

步驟二建立新的Table Type(用舊Table Type的名子)。

步驟三用sys.sp_refreshsqlmodule關聯Stored Procedure跟新建立的Table Type回來。

步驟四砍掉舊的Table Type。

以下為程式碼範例

--舊的Table Type給Rename掉
EXEC sys.sp_rename 'dbo.EmpNoArray', 'zEmpNoArray';
GO


--建立新的Table Type
CREATE TYPE [dbo].[EmpNoArray] AS TABLE(
    [item] [nvarchar](10) NULL
);
GO


--關聯Stored Procedure跟新建立的Table Type回來
DECLARE @Name NVARCHAR(776);
 
DECLARE REF_CURSOR CURSOR FOR
SELECT referencing_schema_name + '.' + referencing_entity_name
FROM sys.dm_sql_referencing_entities('dbo.EmpNoArray', '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


--砍掉舊的Table Type
DROP TYPE dbo.zEmpNoArray;
GO