Sql-Server

ALTER TYPE 不可用那麼如何更改類型?

  • June 22, 2020

我知道關於這個問題還有其他問題,但是這個問題真的很簡單,所以我不是在尋找一個複雜的答案!

所以我有這個腳本:

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。有沒有一種簡單的方法可以做到這一點?

最簡單的方法是使用更新的定義創建一個類型,然後您可以一次(或一次)更改受影響的對像以引用新名稱。你不能拉桌布。

我在這裡寫了一篇關於這個的文章:

基本流程是:

  1. 創建一個新類型
  2. 確定哪些對象引用舊類型
  3. 手動更改這些對像以指向新類型(或至少那些需要使用新類型的對象)
  4. 可選地,刪除舊類型

棘手的部分是#2,人們會手動搜尋和啄,或嘗試解析OBJECT_DEFINITIONor sys.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

另一種查找引用對象的方法是。

  1. 轉到對象資源管理器 > 擴展數據庫 > 擴展可程式性 > 擴展類型 > 使用者定義的表類型。
  2. 右鍵點擊表類型並點擊(左)查看依賴項

現在您可以生成所有對象的腳本,刪除它並在更新表類型後重新創建。

謝謝!

引用自:https://dba.stackexchange.com/questions/242497