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