改变SQL Server用户定义类型(Alter user defined type in Sql

我在DB中创建一些用户定义类型，如下

CREATE TYPE [dbo].[StringID] FROM [nvarchar](20) NOT NULL

并分配给各种表。 我在数据库表是在不同的模式（不仅DBO）

但我意识到我需要更大的领域，我需要改变，例如，从为nvarchar到nvarchar的增加，但没有ALTER TYPE语句

我需要一个脚本，临时表/光标什么，并保存有在使用我的类型的所有表和字段。 然后，更改现有字段来基本类型 - 例如，从客户ID [的StringID]到的CustID [nvarchar的（20）]。 删除用户类型和新类型重新创建 - 如为nvarchar（50），最后重新设置为用户类型字段

我没有规则的类型定义，这样就不必放弃规则，重新添加

我不是很熟悉的T-SQL，所以任何帮助表示赞赏。

Answer 1:

这是我正常使用的，虽然有点手册：

/* Add a 'temporary' UDDT with the new definition */ 
exec sp_addtype t_myudt_tmp, 'numeric(18,5)', NULL 


/* Build a command to alter all the existing columns - cut and 
** paste the output, then run it */ 
select 'alter table dbo.' + TABLE_NAME + 
       ' alter column ' + COLUMN_NAME + ' t_myudt_tmp' 
from INFORMATION_SCHEMA.COLUMNS 
where DOMAIN_NAME = 't_myudt' 

/* Remove the old UDDT */ 
exec sp_droptype t_mydut


/* Rename the 'temporary' UDDT to the correct name */ 
exec sp_rename 't_myudt_tmp', 't_myudt', 'USERDATATYPE' 

Answer 2:

我们使用以下方法，它使我们能够重新创建一个从头开始的类型，这是“一个开始”。 它重命名现有的类型，创建类型，重新编译存储的特效，然后删除旧的类型。 这需要照顾的场景中简单地删除旧类型定义失败，因为该类型的引用。

使用例：

exec RECREATE_TYPE @schema='dbo', @typ_nme='typ_foo', @sql='AS TABLE([bar] varchar(10) NOT NULL)'

码：

CREATE PROCEDURE [dbo].[RECREATE_TYPE]
    @schema     VARCHAR(100),       -- the schema name for the existing type
    @typ_nme    VARCHAR(128),       -- the type-name (without schema name)
    @sql        VARCHAR(MAX)        -- the SQL to create a type WITHOUT the "CREATE TYPE schema.typename" part
AS DECLARE
    @scid       BIGINT,
    @typ_id     BIGINT,
    @temp_nme   VARCHAR(1000),
    @msg        VARCHAR(200)
BEGIN
    -- find the existing type by schema and name
    SELECT @scid = [SCHEMA_ID] FROM sys.schemas WHERE UPPER(name) = UPPER(@schema);
    IF (@scid IS NULL) BEGIN
        SET @msg = 'Schema ''' + @schema + ''' not found.';
        RAISERROR (@msg, 1, 0);
    END;
    SELECT @typ_id = system_type_id FROM sys.types WHERE UPPER(name) = UPPER(@typ_nme);
    SET @temp_nme = @typ_nme + '_rcrt'; -- temporary name for the existing type

    -- if the type-to-be-recreated actually exists, then rename it (give it a temporary name)
    -- if it doesn't exist, then that's OK, too.
    IF (@typ_id IS NOT NULL) BEGIN
        exec sp_rename @objname=@typ_nme, @newname= @temp_nme, @objtype='USERDATATYPE'
    END;    

    -- now create the new type
    SET @sql = 'CREATE TYPE ' + @schema + '.' + @typ_nme + ' ' + @sql;
    exec sp_sqlexec @sql;

    -- if we are RE-creating a type (as opposed to just creating a brand-spanking-new type)...
    IF (@typ_id IS NOT NULL) BEGIN
        exec recompile_prog;    -- then recompile all stored procs (that may have used the type)
        exec sp_droptype @typename=@temp_nme;   -- and drop the temporary type which is now no longer referenced
    END;    
END

GO


CREATE PROCEDURE [dbo].[recompile_prog]
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @v TABLE (RecID INT IDENTITY(1,1), spname sysname)
    -- retrieve the list of stored procedures
    INSERT INTO 
        @v(spname) 
    SELECT 
        '[' + s.[name] + '].[' + items.name + ']'     
    FROM 
        (SELECT sp.name, sp.schema_id, sp.is_ms_shipped FROM sys.procedures sp UNION SELECT so.name, so.SCHEMA_ID, so.is_ms_shipped FROM sys.objects so WHERE so.type_desc LIKE '%FUNCTION%') items
        INNER JOIN sys.schemas s ON s.schema_id = items.schema_id    
        WHERE is_ms_shipped = 0;

    -- counter variables
    DECLARE @cnt INT, @Tot INT;
    SELECT @cnt = 1;
    SELECT @Tot = COUNT(*) FROM @v;
    DECLARE @spname sysname
    -- start the loop
    WHILE @Cnt <= @Tot BEGIN    
        SELECT @spname = spname        
        FROM @v        
        WHERE RecID = @Cnt;
        --PRINT 'refreshing...' + @spname    
        BEGIN TRY        -- refresh the stored procedure        
            EXEC sp_refreshsqlmodule @spname    
        END TRY    
        BEGIN CATCH        
            PRINT 'Validation failed for : ' + @spname + ', Error:' + ERROR_MESSAGE();
        END CATCH    
        SET @Cnt = @cnt + 1;
    END;

END

Answer 3:

有一个更全面的脚本的一个很好的例子

值得一提的是，该脚本将包括意见，如果您有任何。 我跑它和代替exec'ing联生成的脚本作为输出，然后我调整和跑。

此外，如果您在使用用户defeined类型的函数/存储过程，你需要运行脚本之前丢弃这些。

教训：在未来，不与他们更麻烦比他们的价值的UDT麻烦。

SET NOCOUNT ON

DECLARE @udt VARCHAR(150)
DECLARE @udtschema VARCHAR(150)
DECLARE @newudtschema VARCHAR(150)
DECLARE @newudtDataType VARCHAR(150)
DECLARE @newudtDataSize smallint
DECLARE @OtherParameter VARCHAR(50)

SET @udt = 'Name' -- Existing UDDT
SET @udtschema = 'dbo' -- Schema of the UDDT
SET @newudtDataType = 'varchar' -- Data type for te new UDDT
SET @newudtDataSize = 500 -- Lenght of the new UDDT
SET @newudtschema = 'dbo' -- Schema of the new UDDT
SET @OtherParameter = ' NULL' -- Other parameters like NULL , NOT NULL
DECLARE @Datatype VARCHAR(50),
    @Datasize SMALLINT

DECLARE @varcharDataType VARCHAR(50)

DECLARE @Schemaname VARCHAR(50),
    @TableName VARCHAR(50),
    @FiledName VARCHAR(50)

CREATE TABLE #udtflds
    (
      Schemaname VARCHAR(50),
      TableName VARCHAR(50),
      FiledName VARCHAR(50)
    )

SELECT TOP 1
        @Datatype = Data_type,
        @Datasize = character_maximum_length
FROM    INFORMATION_SCHEMA.COLUMNS
WHERE   Domain_name = @udt
        AND Domain_schema = @udtschema

SET @varcharDataType = @Datatype
IF @DataType Like '%char%'
    AND @Datasize IS NOT NULL
    AND ( @newudtDataType <> 'varchar(max)'
          OR @newudtDataType <> 'nvarchar(max)'
        )
    BEGIN
        SET @varcharDataType = @varcharDataType + '('
            + CAST(@Datasize AS VARCHAR(50)) + ')'
    END

INSERT  INTO #udtflds
        SELECT  TABLE_SCHEMA,
                TABLE_NAME,
                Column_Name
        FROM    INFORMATION_SCHEMA.COLUMNS
        WHERE   Domain_name = @udt
                AND Domain_schema = @udtschema

DECLARE @exec VARCHAR(500)

DECLARE alter_cursor CURSOR
    FOR SELECT  Schemaname,
                TableName,
                FiledName
        FROM    #udtflds

OPEN alter_cursor
FETCH NEXT FROM alter_cursor INTO @Schemaname, @TableName, @FiledName

WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @exec = 'Alter Table ' + @Schemaname + '.' + @TableName
            + '  ALTER COLUMN ' + @FiledName + ' ' + @varcharDataType
        EXECUTE ( @exec
               )
        FETCH NEXT FROM alter_cursor INTO @Schemaname, @TableName, @FiledName

    END

CLOSE alter_cursor

SET @exec = 'DROP TYPE [' + @udtschema + '].[' + @udt + ']'
EXEC ( @exec
    )

SET @varcharDataType = @newudtDataType

IF @newudtDataType Like '%char%'
    AND @newudtDataSize IS NOT NULL
    AND ( @newudtDataType <> 'varchar(max)'
          OR @newudtDataType <> 'nvarchar(max)'
        )
    BEGIN
        SET @varcharDataType = @varcharDataType + '('
            + CAST(@newudtDataSize AS VARCHAR(50)) + ')'
    END

SET @exec = 'CREATE TYPE [' + @newudtschema + '].[' + @udt + '] FROM '
    + @varcharDataType + ' ' + @OtherParameter
EXEC ( @exec
    )

OPEN alter_cursor
FETCH NEXT FROM alter_cursor INTO @Schemaname, @TableName, @FiledName

WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @exec = 'Alter Table ' + @Schemaname + '.' + @TableName
            + '  ALTER COLUMN ' + @FiledName + ' ' + '[' + @newudtschema
            + '].[' + @udt + ']'
        EXECUTE ( @exec
               )
        FETCH NEXT FROM alter_cursor INTO @Schemaname, @TableName, @FiledName
    END

CLOSE alter_cursor
DEALLOCATE alter_cursor
SELECT  *
FROM    #udtflds

DROP TABLE #udtflds

1 ： http://www.sql-server-performance.com/2008/how-to-alter-a-uddt/已经取代http://www.sql-server-performance.com/faq/How_to_alter_a%20_UDDT_p1。 ASPX

Answer 4:

由于devio说，没有办法，如果它在使用时只需编辑UDT。

一个工作全面通过手机短信，对我的工作是要生成创建脚本，并进行适当的修改; 重命名现有UDT; 运行创建脚本; 重新编译存储过程有关，丢弃重命名的版本。

Answer 5:

如果用户定义的类型只在表定义中使用此处提供的解决方案只能适用，如果UDT列没有索引。

一些开发商也有SP的和函数使用UDT参数，它也不包括在内。 （见注释罗宾的链接 ，并在连接进入 ）

从2007年进入连接终于在3年后关闭：

感谢您提出这个建议，但它的优先级相对于我们的队列中的许多其他项目给出的，这是不可能的，我们将真正完成它。 因此，我们正在关闭这个建议作为“不会修复”。

我试图解决一个类似的问题改变XML架构集合 ，和步骤似乎大多适用于ALTER TYPE，太：

要删除UDT，以下步骤是必须的：

• 如果一个表列引用UDT，它必须被转换为基础类型
• 如果表列有一个默认的约束，删除默认的约束
• 如果一个过程或函数具有UDT参数，过程或函数必须被丢弃
• 如果有一个UDT列的索引，该指数已被丢弃
• 如果该索引是主键，所有外键都被丢弃
• 如果有根据UDT列计算列，计算列必须被丢弃
• 如果有这些计算列索引，索引必须被丢弃
• 如果基于包含UDT列的表有架构绑定视图，函数或过程，这些对象必须被丢弃

Answer 6:

要做到这一点，最简单的方法是通过Visual Studio的对象资源管理器，也支持在社区版。

一旦你作出了连接到SQL Server，浏览到该类型，单击鼠标右键，选择查看代码，使您更改用户定义类型的模式，然后单击更新。 Visual Studio中，你应该显示所有依赖该对象，并生成脚本来更新类型和重新编译依赖。

Answer 7:

新的答案，一个老问题：

Visual Studio的数据库项目处理的下降，当你部署的变化重新处理。 它会删除使用尿分流旱厕，然后删除并重新创建数据类型后重新创建这些存储的特效。

Answer 8:

1.Rename老UDT，
2.Execute查询，3.Drop老UDT。

Answer 9:

简单DROP TYPE第一则CREATE TYPE与修正/变更一次？

有一个简单的测试，看看如果你删除它之前...就像一个表，PROC或函数定义它 - 如果我没有在工作中我会看是什么吗？

（我只脱脂上面太...如果我读错我提前道歉;！）

文章来源: Alter user defined type in Sql Server