SQL 删除用户自定义表类型

--判断Type是否存在，如果存在，重命名，随后之后才再删除，否则无法直接删除
IF EXISTS (SELECT 1 FROM sys.types t join sys.schemas s on t.schema_id=s.schema_id 
                        and t.name='TBType_QuotePrice' and s.name='dbo')
   EXEC sys.sp_rename 'dbo.TBType_QuotePrice', 'obsoleting_TBType_QuotePrice';
GO


--重建TYPE，比如原来是四个字段，现在想修改为三个字段，或者原来有三个字段想加一个字段变成四个字段
CREATE TYPE [dbo].[TBType_QuotePrice] AS TABLE(
	[BillNumberID] [numeric](10, 0) NULL,
	[PtypeID] [varchar](50) NULL
)
GO

--将原来引用将要删除的TYPE全部重建一遍，否则原始存储过程会报错
DECLARE @Name NVARCHAR(500);
DECLARE REF_CURSOR CURSOR FOR
SELECT OBJECT_SCHEMA_NAME ( referencing_id ) AS referencing_schema_name,OBJECT_NAME ( referencing_id ) AS referencing_entity_name 
	FROM  sys.sql_expression_dependencies WHERE referenced_entity_name = 'TBType_QuotePrice'
--SELECT referencing_schema_name + '.' + referencing_entity_name
--FROM sys.dm_sql_referencing_entities('dbo.TBType_QuotePrice', '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_TBType_QuotePrice' and s.name='dbo')
   DROP TYPE dbo.obsoleting_TBType_QuotePrice
GO

--最后执行授权
GRANT EXECUTE ON TYPE::dbo.TBType_QuotePrice TO public
GO