關於SQL Server中修改“用戶自定義表類型”的問題

本文出處：http://www.cnblogs.com/wy123/p/7282682.html 
（保留出處並非什麼原創作品權利，本人拙作還遠遠達不到，僅僅是為了鏈接到原文，因為後續對可能存在的一些錯誤進行修正或補充，無他）

SQL Server開發過程中，為了傳入數據集類型的變數（比如接受C#中的DataTable類型變數），需要定義“用戶自定義表類型”，通過“用戶自定義表類型”可以接收二維數據集作為參數，
在需要修改“用戶自定義表類型”的時候，增加欄位，刪除欄位，修改欄位類型等，它沒有像表一樣的alter table語法來進行修改。
只能通過刪除重建來實現，但是在刪除“用戶自定義表類型”的時候會提示有對象引用它（某些存儲過程用到了這個“用戶自定義表類型”），因此無法刪除。
為了達到公用的目的，有時候一個TableType可以在多個地方分別被引用到，這樣的話，勢必要先刪除所有的引用了這個“用戶自定義表類型”的對象（存儲過程等）
如果這個“用戶自定義表類型”被多個存儲過程引用，那麼就要分別刪除多個引用了“用戶自定義表類型”的存儲過程，然後修改“用戶自定義表類型”，在重建存儲過程，這樣做起來似乎有點繞，
這個問題可以用過EXEC sys.sp_refreshsqlmodule這個系統函數來簡介實現“用戶自定義表類型”的定義

TableType的基本使用

 如下創建一個用戶自定義表類型

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

 創建兩個存儲過程，分別用到了上面定義的用戶自定義表類型，模擬用戶自定義表類型被引用的情況

 此時的存儲過程可以接收TableType參數並正常運行

TableType的修改

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

那麼只能通過刪除TableType的方法來重建這個TableType，當刪除的時候，仍然報錯，提示“因為它正由對象 '***' 引用。可能還有其他對象在引用此類型。”

此時只能刪除引用了這個TableType的對象來解決，下麵可以查到那些對象引用了某一個TableType，然後分別刪除，重建TableType，再重建存儲過程，有點繞彎子。

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

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

重建TableType之後，先前存儲過程中用到這個TableType的存儲過程是無法編譯通過的

此時就需要重新刷新引用對象的定義

刷新完成之後，原存儲過程就可以正常編譯了

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

　　 這樣子，整個過程就無需因為修改TableType的定義而刪除引用了TableType的對象了，在修改了TableType的定義之後，引用了這個TableType的對象可以正常運行，也可以根據修改之後的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，比如原來是四個欄位，現在想修改為三個欄位，或者原來有三個欄位想加一個欄位變成四個欄位
CREATE TYPE dbo.MyTableType AS TABLE(
    Id INT NOT NULL,
    Name VARCHAR(255) NOT NULL，
　　 Remark VARCHAR(255)

)
GO

--將原來引用將要刪除的TYPE全部重建一遍，否則原始存儲過程會報錯
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(被第一步重名的那個)
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

--最後執行授權
GRANT EXECUTE ON TYPE::dbo.MyTableType TO public
GO

總結：

　　TableType可以方便地接受二維數據作為參數，從而可以達到批量處理數據的目的，避免傳遞進去一大堆字元串，然後在對字元串解析的做法，從而可以在一定程度上提高sql的運行效率。
　　 不過TableType的修改確實存在一定的問題，直接修改TableType會存在級聯刪除資料庫對象的情況，可以通過“曲線救國”的方式，來減小工作量的情況下修改TableType。