Изменить пользовательский тип на сервере Sql

Ответ 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' 

Ответ 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 OK, too.
    IF (@typ_id IS NOT NULL) BEGIN
        exec sp_rename @[email protected]_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 @[email protected]_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

Ответ 3

там хороший пример более полного script здесь

Стоит отметить, что этот script будет включать в себя представления, если они есть. Я запускал его и вместо exec'ing inline генерировал script в качестве вывода, который я затем настраивал и запускал.

Кроме того, если у вас есть функции /sprocs с использованием пользовательских типов, вам необходимо отбросить их перед запуском script.

Извлеченные уроки: в будущем, не беспокойтесь о 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

Ответ 4

Поскольку devio говорит, что нет способа просто редактировать UDT, если он используется.

Работая вокруг SMS, который работал у меня, было создание create script и внесение соответствующих изменений; переименовать существующий UDT; запустите create script; перекомпилируйте связанные sprocs и отпустите переименованную версию.

Ответ 5

Представленные здесь решения могут применяться только в том случае, если пользовательские типы используются только в определениях таблиц, а если столбцы UDT не индексируются.

Некоторые разработчики также имеют SP и функции, использующие параметры UDT, которые также не рассматриваются. (см. комментарии ссылка Robin и в Connect entry)

Запись Connect из 2007, наконец, была закрыта через 3 года:

Спасибо за отправку этого предложение, но с учетом его приоритета по сравнению со многими другими пунктами в наша очередь, маловероятно, что мы будем на самом деле завершить его. Таким образом, мы закрывая это предложение как "привычное исправить".

Я попытался решить подобную проблему ИЗМЕНЯЮЩИЕ КОЛЛЕКЦИИ XML SCHEMA, и этапы, похоже, в основном относятся к ALTER TYPE:

Чтобы удалить UDT, необходимо выполнить следующие шаги:

• Если столбец таблицы ссылается на UDT, он должен быть преобразован в базовый тип
• Если столбец таблицы имеет ограничение по умолчанию, снимите ограничение по умолчанию
• Если в процедуре или функции есть параметры UDT, процедура или функция должны быть удалены
• Если в столбце UDT есть индекс, индекс должен быть отброшен
• Если индекс является первичным ключом, все внешние ключи должны быть удалены.
• Если есть расчетные столбцы на основе столбца UDT, вычисленные столбцы должны быть отброшены
• Если на этих вычисленных столбцах есть индексы, индексы должны быть отброшены
• Если есть связанные с схемой представления, функции или процедуры, основанные на таблицах, содержащих столбцы UDT, эти объекты должны быть удалены

Ответ 6

Новый ответ на старый вопрос:

Проекты базы данных Visual Studio обрабатывают процесс удаления и воссоздания при развертывании изменений. Он выкинет хранимые procs, которые используют UDDT, а затем воссоздают их после удаления и воссоздания типа данных.

Ответ 7

Самый простой способ сделать это - через объектный проводник Visual Studio, который также поддерживается в версии сообщества.

Как только установил соединение на SQL-сервер, перейдите к типу, щелкните правой кнопкой мыши и выберите Просмотреть код, внесите изменения в схему определяемого пользователем типа и нажмите обновление. Visual Studio должна показать вам все зависимости для этого объекта и сгенерировать скрипты для обновления типа и перекомпиляции зависимостей.

Ответ 8

1.Назовите старый UDT,
2. Выполнить запрос, 3. Поверните старый UDT.

Ответ 9

Я столкнулся с этой проблемой с пользовательскими типами в хранимых процедурах и решил ее с помощью сценария ниже. Я не до конца понял сценарии, приведенные выше, и я следую правилу "если ты не знаешь, что он делает, не делай этого".

В двух словах, я переименовываю старый тип и создаю новый с исходным именем типа. Затем я говорю SQL Server обновить информацию о каждой хранимой процедуре, используя пользовательский тип. Вы должны сделать это, так как все по-прежнему "компилируется" со ссылкой на старый тип, даже с переименованием. В этом случае тип, который мне нужно было изменить, был "PrizeType". Надеюсь, это поможет. Я тоже ищу отзывы, поэтому учусь :)

Обратите внимание, что вам может потребоваться перейти к Программируемость> Типы> [Соответствующий тип пользователя] и удалить объект. Я обнаружил, что DROP TYPE не всегда отбрасывает тип даже после использования оператора.

/* Rename the UDDT you want to replace to another name */ 
exec sp_rename 'PrizeType', 'PrizeTypeOld', 'USERDATATYPE';

/* Add the updated UDDT with the new definition */ 
CREATE TYPE [dbo].[PrizeType] AS TABLE(
    [Type] [nvarchar](50) NOT NULL,
    [Description] [nvarchar](max) NOT NULL,
    [ImageUrl] [varchar](max) NULL
);

/* We need to force stored procedures to refresh with the new type... let take care of that. */
/* Get a cursor over a list of all the stored procedures that may use this and refresh them */
declare sprocs cursor
  local static read_only forward_only
for
    select specific_name from information_schema.routines where routine_type = 'PROCEDURE'

declare @sprocName varchar(max)

open sprocs
fetch next from sprocs into @sprocName
while @@fetch_status = 0
begin
    print 'Updating ' + @sprocName;
    exec sp_refreshsqlmodule @sprocName
    fetch next from sprocs into @sprocName
end
close sprocs
deallocate sprocs

/* Drop the old type, now that everything been re-assigned; must do this last */
drop type PrizeTypeOld;

Ответ 10

Простой DROP TYPE сначала, затем CREATE TYPE снова с исправлениями/изменениями?

Существует простой тест, чтобы определить, определено ли это, прежде чем вы его удалите... как таблица, proc или function - если бы я не был на работе, я бы посмотрел, что это такое?

(Я только просматривал выше... если я прочитал это неправильно, я извиняюсь заранее!;)