from - transact sql tutorial

Modificar el tipo definido por el usuario en el servidor SQL

Question

Creé pocos tipos definidos por el usuario en DB como abajo

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

y asignados a varias mesas. Mis tablas en db están en varios esquemas (no solo dbo)

Pero me di cuenta de que necesito un campo más grande, y necesito modificar, por ejemplo, aumentar de nvarchar a nvarchar, pero no hay una declaración ALTER TYPE

Necesito una secuencia de comandos que sirva de tabla temporal / cursor y guarde allí todas las tablas y campos donde se utiliza mi tipo. Luego cambie los campos existentes al tipo base, por ejemplo, de CustID [StringID] a CustID [nvarchar (20)]. Elimine el tipo de usuario y vuélvalo a crear con un nuevo tipo, por ejemplo, nvarchar (50) y, finalmente, vuelva a configurar los campos al tipo de usuario

No tengo reglas definidas en los tipos, así que no tengo que descartar reglas y volver a agregarlas

No estoy muy familiarizado con T-Sql, por lo que se agradece cualquier ayuda.

Answer 1

1.Nombrar el antiguo UDT,
2. Ejecutar consulta, 3.Desechar el antiguo UDT.

Answer 2

Como dice Devio, no hay forma de editar un UDT simplemente si está en uso.

Una ronda de trabajo a través de SMS que funcionó para mí fue generar un script de creación y hacer los cambios apropiados; renombrar el UDT existente; ejecutar el script de creación; recompile los sprocs relacionados y descarte la versión renombrada.

Answer 3

Estamos utilizando el siguiente procedimiento, nos permite recrear un tipo desde cero, que es "un comienzo". Renombra el tipo existente, crea el tipo, recompila los procesos almacenados y luego elimina el tipo anterior. Esto se ocupa de los escenarios en los que simplemente al eliminar la antigua definición de tipo se produce un error debido a las referencias a ese tipo.

Ejemplo de uso:

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

Código:

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 4

Esto es lo que normalmente uso, aunque sea un poco manual:

/* 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 5

La forma más sencilla de hacerlo es a través del explorador de objetos de Visual Studio, que también se admite en la edición comunitaria.

Una vez que haya establecido una conexión con el servidor SQL, busque el tipo, haga clic con el botón derecho y seleccione Ver código, realice los cambios en el esquema del tipo definido por el usuario y haga clic en actualizar. Visual Studio debería mostrarle todas las dependencias para ese objeto y generar scripts para actualizar el tipo y recompilar las dependencias.

Answer 6

Las soluciones proporcionadas aquí solo se pueden aplicar si los tipos definidos por el usuario se usan solo en las definiciones de tabla, y si las columnas UDT no están indexadas.

Algunos desarrolladores también tienen SP y funciones que utilizan parámetros UDT, que tampoco están cubiertos. (ver comentarios en http://www.sql-server-performance.com/faq/How_to_alter_a%20_UDDT_p1.aspx y en la connect.microsoft.com/SQLServer/feedback/details/319134/… )

La entrada Connect del 2007 finalmente se cerró después de 3 años:

Gracias por enviar esta sugerencia, pero dada su prioridad en relación con los muchos otros elementos en nuestra cola, es poco probable que la completemos. Como tal, estamos cerrando esta sugerencia como "no arreglaremos".

Intenté resolver un problema similar ALTERANDO COLECCIONES DE ESQUEMA XML , y los pasos parecen aplicarse principalmente a ALTER TYPE, también:

Para soltar un UDT, son necesarios los siguientes pasos:

• Si una columna de tabla hace referencia al UDT, debe convertirse al tipo subyacente
• Si la columna de la tabla tiene una restricción predeterminada, elimine la restricción predeterminada
• Si un procedimiento o función tiene parámetros UDT, el procedimiento o la función debe eliminarse
• Si hay un índice en una columna UDT, el índice debe ser eliminado
• Si el índice es una clave principal, todas las claves externas deben ser eliminadas
• Si hay columnas computadas basadas en una columna UDT, las columnas computadas deben ser eliminadas
• Si hay índices en estas columnas calculadas, los índices deben ser eliminados
• Si hay vistas, funciones o procedimientos vinculados a esquemas basados ​​en tablas que contienen columnas UDT, estos objetos se deben eliminar.

Answer 7

Nueva respuesta a una vieja pregunta:

Los proyectos de base de datos de Visual Studio manejan el proceso de soltar y volver a crear cuando se implementan cambios. Eliminará los procesos almacenados que usan UDDT y luego los recreará después de eliminar y recrear el tipo de datos.

Answer 8

Primero, ¿primero DROP TYPE luego CREATE TYPE nuevamente con correcciones / alteraciones?

Hay una prueba simple para ver si está definida antes de que la sueltes ... como una tabla, un proceso o una función. Si no estuviera trabajando, ¿qué aspecto tendría?

(Solo lo he visto por encima también ... si lo leí mal, ¡me disculpo por adelantado!)

Answer 9

Hay un buen ejemplo de un script más completo aquí.

Vale la pena señalar que esta secuencia de comandos incluirá vistas si tiene alguna. Lo ejecuté y, en lugar de ejecutarlo en línea, generé un script como salida que luego modifiqué y ejecuté.

Además, si tiene funciones / funciones utilizando los tipos definidos por el usuario, deberá eliminarlos antes de ejecutar su script.

Lección aprendida: en el futuro, no se moleste con los UDT, son más problemáticos de lo que valen.

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/ ha reemplazado http://www.sql-server-performance.com/faq/How_to_alter_a%20_UDDT_p1.aspx