복붙노트

[SQL] 는 SQL Server의 알터 사용자 정의 유형

SQL

는 SQL Server의 알터 사용자 정의 유형

나는 다음과 같이 DB에 몇 가지 사용자 정의 유형을 생성

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

여러 테이블에 할당. DB에서 내 테이블 (뿐만 아니라 DBO) 다양한 스키마에

하지만 내가 더 큰 필드가 필요 실현, 나는 NVARCHAR에 NVARCHAR에서, 예컨대 증가를 변경해야되지만 ALTER TYPE 문이 없다

나는 스크립트를 필요로하는 임시 테이블 / 커서 무엇이든지 내 타입이 사용되는 모든 테이블과 필드가 저장. 그런 다음 기본 형식으로 기존 필드를 변경 - 예를 들어, CustID [StringID]에서 CustID 내지 [NVARCHAR (20)]. 사용자 유형을 삭제하고 새로운 유형으로 다시 작성 - 예를 NVARCHAR (50) 마지막으로 사용자 유형에 다시 필드를 설정

그래서 규칙을 삭제해야하고 다시 추가하지 않습니다, 규칙 유형에 정의가 없습니다

어떤 도움이 감사합니다, 그래서 나는, T-SQL에 익숙하지 않다.

해결법

  1. ==============================

    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. ==============================

    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
  3. ==============================

    3.여기에 좀 더 포괄적 인 스크립트의 좋은 예는있다

    여기에 좀 더 포괄적 인 스크립트의 좋은 예는있다

    당신은 어떤이있는 경우이 스크립트는 전망이 포함됩니다 것을주의 그것의 가치. 나는 그것을 실행하는 대신 exec'ing의 인라인 그때 불통하고 실행 한 출력으로 스크립트를 생성합니다.

    당신은 사용자 정의 유형을 사용하여 기능 / 발동이있는 경우 또한, 당신은 당신의 스크립트를 실행하기 전에 그 드롭해야합니다.

    수업은 배운 : 미래에 그들이있는 거 가치보다 더 많은 번거 로움있어 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 교체했다. 영문

  4. ==============================

    4.devio 말한다대로 사용의 경우 단순히 편집 UDT를 할 수있는 방법이 없습니다.

    devio 말한다대로 사용의 경우 단순히 편집 UDT를 할 수있는 방법이 없습니다.

    업무와 라운드 나를 위해 일한 SMS를 통해이 스크립트를 작성하고 적절한 변경을 생성했다; 기존의 UDT의 이름을 변경; (가) 스크립트를 만들고 실행; 관련 sprocs가 다시 컴파일하고 이름을 바꾼 버전을 놓습니다.

  5. ==============================

    5.여기에 제공된 솔루션은 사용자 정의 형식만을 정의 테이블에서 사용하는 경우에만 적용 할 수 있고, UDT 칼럼 인덱싱되지 않은 경우.

    여기에 제공된 솔루션은 사용자 정의 형식만을 정의 테이블에서 사용하는 경우에만 적용 할 수 있고, UDT 칼럼 인덱싱되지 않은 경우.

    일부 개발자들은 또한 SP의 및 중 하나에 포함되지 않습니다 UDT 매개 변수를 사용하여 기능을 가지고 있습니다. (로빈의 링크와 연결 항목에 주석을 참조)

    2007에서 연결 항목이 마지막으로 삼년 후 닫혔습니다 :

    나는 비슷한 문제의 변화에 ​​XML 스키마 컬렉션을 해결하기 위해 노력하고 단계는 대부분 너무 ALTER 유형에 적용하는 것 :

    UDT의를 삭제하려면 다음 단계가 필요합니다 :

  6. ==============================

    6.이 작업을 수행하는 가장 간단한 방법은 커뮤니티 에디션에서 지원하는 비주얼 스튜디오의 개체 탐색기를 통해입니다.

    이 작업을 수행하는 가장 간단한 방법은 커뮤니티 에디션에서 지원하는 비주얼 스튜디오의 개체 탐색기를 통해입니다.

    당신이 유형에 SQL 서버, 검색으로 연결을 설정 한 후 마우스 오른쪽 단추로 클릭하고 코드보기를 선택, 사용자 정의 형의 스키마 변경 사항을 확인하고 업데이트를 클릭합니다. Visual Studio에서 해당 객체에 대해 당신에게 모든 종속성을 표시하고 유형과 재 컴파일 의존성을 업데이트하는 스크립트를 생성해야합니다.

  7. ==============================

    7.오래된 질문에 새로운 답 :

    오래된 질문에 새로운 답 :

    변경 사항을 배포 할 때 Visual Studio를 데이터베이스 프로젝트 드롭 및 재 작성 과정을 처리합니다. 그것은 UDDTs을 사용하고 삭제하고 데이터 형식을 재현 한 후이를 다시 저장 발동 떨어질 것이다.

  8. ==============================

    8.내가 저장 프로 시저에서 사용자 정의 유형이 문제로 달리고, 아래의 스크립트를 해결했다. 나는 완전히 위의 스크립트를 이해하지 않았고, 나는 "당신이 무엇을하는지 모른다면, 그것을하지 않는다"의 규칙을 따릅니다.

    내가 저장 프로 시저에서 사용자 정의 유형이 문제로 달리고, 아래의 스크립트를 해결했다. 나는 완전히 위의 스크립트를 이해하지 않았고, 나는 "당신이 무엇을하는지 모른다면, 그것을하지 않는다"의 규칙을 따릅니다.

    간단히 말해서, 나는 오래된 유형의 이름을 변경하고, 원래의 형 이름으로 새로 만듭니다. 그럼, 사용자 정의 형식을 사용하여 각 저장 프로 시저에 대한 세부 정보를 새로 고치려면 SQL 서버를 말한다. 모든 것이 여전히 심지어 이름 변경과 함께, 기존의 유형을 참조하여 "컴파일"있는 그대로 당신은이 작업을 수행 할 수 있습니다. 이 경우, 나는 변화에 필요한 유형은 "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's 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's been re-assigned; must do this last */
drop type PrizeTypeOld;
  9. ==============================

    9.이전 UDT를 1.Rename 2.Execute 쿼리 이전 UDT를 3.Drop.

    이전 UDT를 1.Rename 2.Execute 쿼리 이전 UDT를 3.Drop.

  10. ==============================

    10.간단한 DROP 유형은 먼저 수정 / 변경 다시 TYPE을 만드시겠습니까?

    간단한 DROP 유형은 먼저 수정 / 변경 다시 TYPE을 만드시겠습니까?

    내가 직장에서 아니었다면 나는 그게 뭔지 보일 것이다 - 당신이 많은 테이블, 시저 또는 함수처럼 ... 드롭하기 전에 정의 된 경우 볼 수있는 간단한 테스트가있다?

    (내가 잘못 내가 미리 사과 읽는다면 난 단지 ... 너무 이상 미끄러 져;!)

    11. from https://stackoverflow.com/questions/1383494/alter-user-defined-type-in-sql-server by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 어떤 SQL 문은 빠르다? (대 WHERE 음 ...)  (0) 2020.06.05
[SQL] 어떻게 테이블에서 최대 값을 가지는 오라클 시퀀스 시작을 만드는 방법?  (0) 2020.06.05
[SQL] 없는 그룹에 의해 SQL에서 열을 선택합니다  (0) 2020.06.05
[SQL] 경고 : NULL 값은 아쿠아 데이터 스튜디오에서 집계 또는 다른 SET 연산에 의해 제거  (0) 2020.06.05
[SQL] TSQL가 - A는 ... END 블록을 BEGIN의 방법 GO의 내부를 사용 하는가?  (0) 2020.06.05
,

티스토리툴바