前言

SQL Server開發(fā)過程中，為了傳入數(shù)據(jù)集類型的變量（比如接受C#中的DataTable類型變量），需要定義“用戶自定義表類型”，通過“用戶自定義表類型”可以接收二維數(shù)據(jù)集作為參數(shù)，在需要修改“用戶自定義表類型”的時(shí)候，增加字段，刪除字段，修改字段類型等，它沒有像表一樣的alter table語法來進(jìn)行修改。

只能通過刪除重建來實(shí)現(xiàn)，但是在刪除“用戶自定義表類型”的時(shí)候會(huì)提示有對(duì)象引用它（某些存儲(chǔ)過程用到了這個(gè)“用戶自定義表類型”），因此無法刪除。

為了達(dá)到公用的目的，有時(shí)候一個(gè)TableType可以在多個(gè)地方分別被引用到，這樣的話，勢必要先刪除所有的引用了這個(gè)“用戶自定義表類型”的對(duì)象（存儲(chǔ)過程等）

如果這個(gè)“用戶自定義表類型”被多個(gè)存儲(chǔ)過程引用，那么就要分別刪除多個(gè)引用了“用戶自定義表類型”的存儲(chǔ)過程，然后修改“用戶自定義表類型”，在重建存儲(chǔ)過程，這樣做起來似乎有點(diǎn)繞，這個(gè)問題可以用過EXEC sys.sp_refreshsqlmodule這個(gè)系統(tǒng)函數(shù)來簡介實(shí)現(xiàn)“用戶自定義表類型”的定義

TableType的基本使用

如下創(chuàng)建一個(gè)用戶自定義表類型

定義的TableType可以在用戶自定義表類型中找到

創(chuàng)建兩個(gè)存儲(chǔ)過程，分別用到了上面定義的用戶自定義表類型，模擬用戶自定義表類型被引用的情況

此時(shí)的存儲(chǔ)過程可以接收TableType參數(shù)并正常運(yùn)行

TableType的修改

TableType類型不支持alter語法，也即無法直接修改TableType的定義

那么只能通過刪除TableType的方法來重建這個(gè)TableType，當(dāng)刪除的時(shí)候，仍然報(bào)錯(cuò)，提示“因?yàn)樗蓪?duì)象 ‘***’ 引用。可能還有其他對(duì)象在引用此類型。”

此時(shí)只能刪除引用了這個(gè)TableType的對(duì)象來解決，下面可以查到那些對(duì)象引用了某一個(gè)TableType，然后分別刪除，重建TableType，再重建存儲(chǔ)過程，有點(diǎn)繞彎子。

可以先將自定義的某個(gè)TableType重命名，重命名的過程中有一個(gè)警告，這里先忽略它，隨后可以直接Drop Type dbo.MyTableType

刪除原TableType之后，重建（重定義）TableType

重建TableType之后，先前存儲(chǔ)過程中用到這個(gè)TableType的存儲(chǔ)過程是無法編譯通過的

此時(shí)就需要重新刷新引用對(duì)象的定義

刷新完成之后，原存儲(chǔ)過程就可以正常編譯了

最后刪除原始的TableType被重命名的TableType(被第一步重名的那個(gè))

這樣子，整個(gè)過程就無需因?yàn)樾薷腡ableType的定義而刪除引用了TableType的對(duì)象了，在修改了TableType的定義之后，引用了這個(gè)TableType的對(duì)象可以正常運(yùn)行，也可以根據(jù)修改之后的TableType做具體的使用

完整的腳本如下

–判斷Type是否存在，如果存在，重命名，隨后之后才再刪除，否則無法直接刪除
IF EXISTS (SELECT 1 FROM sys.types t join sys.schemas s on t.schema_id=s.schema_id
and t.name=’MyTableType’ and s.name=’dbo’)
EXEC sys.sp_rename ‘dbo.MyTableType’, ‘obsoleting_MyTableType’;
GO

–重建TYPE，比如原來是四個(gè)字段，現(xiàn)在想修改為三個(gè)字段，或者原來有三個(gè)字段想加一個(gè)字段變成四個(gè)字段
CREATE TYPE dbo.MyTableType AS TABLE(
Id INT NOT NULL,
Name VARCHAR(255) NOT NULL，　　 Remark VARCHAR(255)
)
GO

–將原來引用將要?jiǎng)h除的TYPE全部重建一遍，否則原始存儲(chǔ)過程會(huì)報(bào)錯(cuò)
DECLARE @Name NVARCHAR(500);
DECLARE REF_CURSOR CURSOR FOR
SELECT referencing_schema_name + ‘.’ + referencing_entity_name
FROM sys.dm_sql_referencing_entities(‘dbo.MyTableType’, ‘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

–最后刪除原始的被重命名的TableType(被第一步重名的那個(gè))
IF EXISTS (SELECT 1 FROM sys.types t
join sys.schemas s on t.schema_id=s.schema_id
and t.name=’obsoleting_MyTableType’ and s.name=’dbo’)
DROP TYPE dbo.obsoleting_MyTableType
GO

–最后執(zhí)行授權(quán)
GRANT EXECUTE ON TYPE::dbo.MyTableType TO public
GO