Sql-Server
ALTER TYPE 不可用那麼如何更改類型?
我知道關於這個問題還有其他問題,但是這個問題真的很簡單,所以我不是在尋找一個複雜的答案!
所以我有這個腳本:
IF EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'IDListTableType' AND ss.name = N'dbo') DROP TYPE [dbo].[IDListTableType] GO IF NOT EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'IDListTableType' AND ss.name = N'dbo') CREATE TYPE [dbo].[IDListTableType] AS TABLE( [ID] [int] NOT NULL, UNIQUE NONCLUSTERED ( [ID] ASC )WITH (IGNORE_DUP_KEY = OFF) ) GO
執行它時出現錯誤,因為
IDListTableType
在數據庫的其他地方引用。所以我需要一種方法來改變表類型,或者找到並刪除並重新創建所有其他引用的數據庫對象IDListTableType
。有沒有一種簡單的方法可以做到這一點?
最簡單的方法是使用更新的定義創建一個新類型,然後您可以一次(或一次)更改受影響的對像以引用新名稱。你不能拉桌布。
我在這裡寫了一篇關於這個的文章:
基本流程是:
- 創建一個新類型
- 確定哪些對象引用舊類型
- 手動更改這些對像以指向新類型(或至少那些需要使用新類型的對象)
- 可選地,刪除舊類型
棘手的部分是#2,人們會手動搜尋和啄,或嘗試解析
OBJECT_DEFINITION
orsys.sql_modules
,但以下更可靠一點:SELECT s.name, o.name, def = OBJECT_DEFINITION(d.referencing_id) FROM sys.sql_expression_dependencies AS d INNER JOIN sys.objects AS o ON d.referencing_id = o.[object_id] INNER JOIN sys.schemas AS s ON o.[schema_id] = s.[schema_id] WHERE d.referenced_database_name IS NULL AND d.referenced_schema_name = N'dbo' AND d.referenced_entity_name = N'MyType';
所以我需要一種方法來更改表類型,或者查找並刪除並重新創建所有其他引用 IDListTableType 的數據庫對象。有沒有一種簡單的方法可以做到這一點?
首先,正如Aaron Bertrand指出的那樣,您已經是,我們不能 ALTER 表類型,我們可以 CREATE & DROP 它。
因此,要對現有內容進行任何更改,
TABLE TYPE
您需要“刪除”它並使用新定義執行 CREATE TYPE 語句。查找引用對象。
您可以使用以下查詢來查找引用表類型的所有對象。
SELECT OBJECT_NAME(OBJECT_ID),DEFINITION FROM sys.sql_modules WHERE DEFINITION LIKE '%IDListTableType%';
例子:
CREATE TYPE [dbo].[IDListTableType] AS TABLE( [ID] [int] NOT NULL, UNIQUE NONCLUSTERED ( [ID] ASC ) ) GO CREATE PROCEDURE sp_reference_table_type_1 AS DECLARE @Table [dbo].[IDListTableType] DECLARE @I int=1 WHILE @I<=10000 BEGIN INSERT INTO @Table VALUES(@I) END GO CREATE PROCEDURE sp_reference_table_type_2 AS DECLARE @Table [dbo].[IDListTableType] DECLARE @I int=10001 WHILE @I<=20000 BEGIN INSERT INTO @Table VALUES(@I) END
另一種查找引用對象的方法是。
- 轉到對象資源管理器 > 擴展數據庫 > 擴展可程式性 > 擴展類型 > 使用者定義的表類型。
- 右鍵點擊表類型並點擊(左)查看依賴項
現在您可以生成所有對象的腳本,刪除它並在更新表類型後重新創建。
謝謝!