SQL Server에서 사용자 정의 테이블 유형 변경

SQL Server에서 사용자 정의 테이블 유형 변경

SQL Server에서 사용자 정의 테이블 유형을 변경하려면 어떻게 해야 합니까?

제가 아는 바로는 테이블 타입의 변경/변경은 불가능합니다.다른 이름으로 유형을 생성한 다음 이전 유형을 삭제하고 새 이름으로 수정할 수 있습니다.

jkrajes에 대한 크레딧

msdn에 따르면 '사용자 정의 테이블 유형 정의는 생성된 후 수정할 수 없습니다'와 같습니다.

이것은 일종의 해킹이지만 효과가 있는 것 같다.다음은 테이블 유형을 변경하는 단계와 예입니다.한 가지 주의할 점은 테이블유형에 대한 변경이 해당 오브젝트(일반적으로 절차)에 대한 변경을 중단하는 경우 sp_refreshsqlmodule이 실패한다는 것입니다.

1. sp_rename테이블 타입의 이름을 변경하려면 보통 이름의 선두에 z를 추가합니다.
2. 원래 이름과 테이블 유형을 수정해야 하는 모든 내용을 사용하여 새 테이블 유형을 작성합니다.
3. 대로 실행해 .sp_refreshsqlmodule위에.
4. 이름이 변경된 테이블 유형을 삭제합니다.

---

EXEC sys.sp_rename 'dbo.MyTableType', 'zMyTableType';
GO
CREATE TYPE dbo.MyTableType AS TABLE(
    Id INT NOT NULL,
    Name VARCHAR(255) NOT NULL
);
GO
DECLARE @Name NVARCHAR(776);

DECLARE REF_CURSOR CURSOR FOR
SELECT referencing_schema_name + '.' + referencing_entity_name
FROM sys.dm_sql_referencing_entities('dbo.MyTableType', 'TYPE');

OPEN REF_CURSOR;

FETCH NEXT FROM REF_CURSOR INTO @Name;
WHILE (@@FETCH_STATUS = 0)
BEGIN
    EXEC sys.sp_refreshsqlmodule @name = @Name;
    FETCH NEXT FROM REF_CURSOR INTO @Name;
END;

CLOSE REF_CURSOR;
DEALLOCATE REF_CURSOR;
GO
DROP TYPE dbo.zMyTableType;
GO

경고:

이 경우 데이터베이스가 파괴될 수 있으므로 먼저 개발 환경에서 테스트해야 합니다.

다음은 지루함을 최소화하고 오류가 발생하기 쉬운 반자동 스크립트나 값비싼 도구를 필요로 하지 않는 간단한 단계입니다.

Object Explorer Details 창에서 여러 개체에 대한 DROP/CREATE 문을 생성할 수 있습니다(이렇게 생성하면 DROP 및 CREATE 스크립트가 그룹화되므로 드롭 액션과 CREATE 액션 사이에 논리를 삽입하기 쉬워집니다).

1. 문제가 발생할 경우를 대비해 데이터베이스를 백업합니다.
2. 모든 종속성에 대한 DROP/CREATE 문을 자동으로 생성합니다(또는 종속성을 찾는 번거로움을 없애기 위해 모든 "Programmability" 개체에 대해 생성).
3. DROP 문과 CREATE [dependencies]문 사이에(모든 DROP 후, 모든 CREATE 전에) 생성된DROP/CREATE [table type]문을 삽입하고 CREATE TYPE을 사용하여 필요한 내용을 변경합니다.
4. 스크립트를 실행하여 모든 의존관계/UDTT를 삭제한 후 [UDTTs with changes]/의존관계를 재작성합니다.

---

인프라 아키텍처를 변경하는 것이 적절한 소규모 프로젝트가 있는 경우 사용자 정의 테이블 유형을 제거하는 것을 고려해 보십시오.Entity Framework 및 이와 유사한 도구를 사용하면 데이터 로직의 대부분을 유지보수가 용이한 코드 베이스로 이동할 수 있습니다.

여러 개체의 DROP/CREATE 문을 생성하려면 [Database]> [ Tasks ]> [ Generate Scripts ]를 오른쪽 클릭합니다.(아래 스크린샷과 같이).주의:

1. DROP앞에 있는 스테이트먼트 CREATE(START)
2. DROP스테이트먼트가 의존관계 순서로 되어 있다(CREATE의 역).
3. CREATE스테이트먼트가 의존관계 순서로 되어 있다.

Simon Zeinstra가 해결책을 찾았다!

하지만 저는 Visual Studio 커뮤니티 2015를 사용했고 스키마 비교도 하지 않았습니다.

SQL Server 객체 탐색기를 사용하여 DB에서 사용자 정의 테이블 유형을 찾았습니다.마우스 오른쪽 버튼을 클릭하여 테이블 타입을 선택합니다.그러면 IDE의 코드 탭이 열리고 TSQL 코드가 표시되며 편집 가능합니다.정의를 변경하고(내 경우 nvarchar 필드의 크기를 늘렸을 뿐) 탭의 왼쪽 상단에 있는 Update Database 버튼을 클릭합니다.

Hey Presto! - SSMS의 빠른 체크와 udt 정의가 변경되었습니다.

훌륭해. 사이먼 고마워.

Visual Studio에서 데이터베이스 프로젝트를 사용할 수 있는 경우 프로젝트에서 변경하고 스키마 비교를 사용하여 데이터베이스 변경 사항을 동기화할 수 있습니다.

이렇게 하면 종속 개체를 삭제하고 다시 생성하는 작업이 변경 스크립트에 의해 처리됩니다.

이전 테이블 유형을 삭제하고 새 테이블 유형을 생성해야 합니다.그러나 종속성(이를 사용하는 저장 프로시저)이 있는 경우에는 삭제할 수 없습니다.모든 저장 프로시저를 일시적으로 삭제하고 테이블 테이블을 수정한 후 저장 프로시저를 복원하는 프로세스를 자동화하는 방법에 대한 또 다른 답변을 게시했습니다.

내 프로젝트 중 하나에서 사용자 정의 테이블 유형을 변경해야 했습니다.다음은 제가 사용한 단계입니다.

1. 사용자 정의 테이블 유형을 사용하여 모든 SP를 찾습니다.
2. 발견된 모든 SP에 대한 생성 스크립트를 저장합니다.
3. SP를 드롭합니다.
4. 변경할 사용자 정의 테이블에 대한 작성 스크립트를 저장합니다. 4.5 사용자 정의 테이블 유형에 필요한 열 또는 변경 사항을 추가합니다.
5. 사용자 정의 테이블유형을 드롭합니다.
6. 사용자 정의 테이블 유형에 대한 생성 스크립트를 실행합니다.
7. SP의 생성 스크립트를 실행합니다.
8. 그런 다음 SP를 적절히 수정하십시오.

유형을 변경/수정할 수 없습니다.기존 이름을 삭제하고 올바른 이름/데이터 형식으로 다시 생성하거나 새 열을 추가해야 합니다.

이를 위해 두 개의 저장 프로시저를 만들었습니다.첫 번째 거

create_or_syslog_udt_process는 udt 이름을 입력으로 사용하고 udt를 사용하는 저장된 모든 proc/syslog를 드롭하고 모든 프로시저/syslog를 재생성하기 위한 SQL 스크립트를 반환합니다.

두 번째 create_or_alter_udt_post process는 첫 번째 proc에서 출력된 스크립트를 가져와 실행합니다.

2개의 프로세서를 사용하면 다음 방법으로 udt를 변경할 수 있습니다.

1. create_or_display_udt_process를 호출합니다.
2. 새로운 정의를 사용하여 udt를 작성한다.
3. create_or_syslog_udt_post process를 호출합니다.

트랜잭션을 사용하여 오류가 발생할 경우 원래 프로세서가 손실되지 않도록 합니다.

create or ALTER   proc create_or_alter_udt_postprocess(@udt_postprocess_data xml)
as 
begin
    if @udt_postprocess_data is null 
        return;

    declare @obj_cursor cursor 
    set @obj_cursor = cursor fast_forward for 
    select n.c.value('.', 'nvarchar(max)') as definition
    from @udt_postprocess_data.nodes('/Objects/definition') as n(c)

    open @obj_cursor;

    declare @definition nvarchar(max);
    fetch next from @obj_cursor into @definition;
    while (@@fetch_status = 0)
    begin
        exec sp_executesql @stmt= @definition
        fetch next from @obj_cursor into @definition
    end

    CLOSE @obj_cursor;
    DEALLOCATE @obj_cursor; 
end

Create or ALTER   proc create_or_alter_udt_preprocess(@udt nvarchar(200), @udt_postprocess_data xml out) 
AS
    BEGIN
        set @udt_postprocess_data = null;
        if TYPE_ID(@udt) is null
            return;

        declare @drop_scripts nvarchar(max);
        SELECT @drop_scripts =  (
        (select N';'+ drop_script
            from 
        (
        SELECT 
            drop_script = N'drop ' + case sys.objects.type when 'P' then N'proc ' else N'function' end
                + sys.objects.name + N';' + + nchar(10) + nchar(13)
        FROM sys.sql_expression_dependencies d
        JOIN sys.sql_modules m ON m.object_id = d.referencing_id
        JOIN sys.objects ON sys.objects.object_id = m.object_id
        WHERE referenced_id = TYPE_ID(@udt)
        ) dependencies
        FOR XML PATH (''), type
        ).value('.', 'nvarchar(max)')
         ) ;
    
        declare @postprocess_data xml;

        set @udt_postprocess_data =
        (SELECT 
            definition
        FROM sys.sql_expression_dependencies d
        JOIN sys.sql_modules m ON m.object_id = d.referencing_id
        JOIN sys.objects ON sys.objects.object_id = m.object_id
        WHERE referenced_id = TYPE_ID(@udt)
        FOR XML PATH (''), root('Objects'));
        
        exec sp_executesql @stmt= @drop_scripts;
        exec sp_droptype @udt;
  END

사용 예:

begin tran
declare @udt_postprocess_data xml;

exec create_or_alter_udt_preprocess @udt= 'test_list', @udt_postprocess_data = @udt_postprocess_data out;
CREATE TYPE test_list AS TABLE(
    test_name nvarchar(50) NULL
);

exec create_or_alter_udt_postprocess @udt_postprocess_data = @udt_postprocess_data;

commit;

사용 예를 설정하는 코드:

CREATE TABLE [dbo].[test_table](
    [test_id] [int] IDENTITY(1,1) NOT NULL, [test_name] [varchar](20) NULL
) ON [USERDATA]
GO


CREATE TYPE test_list AS TABLE(test_name nvarchar(20) NULL)
GO

create proc add_tests(
@test_list test_list readonly)
as 
begin
    SET NOCOUNT ON;
    insert into test_table(test_name)
    select test_name
    from @test_list;
end;

create proc add_tests2(
@test_list test_list readonly)
as 
begin
    SET NOCOUNT ON;
    insert into test_table(test_name)
    select test_name
    from @test_list;
end;

언급URL : https://stackoverflow.com/questions/11410722/altering-user-defined-table-types-in-sql-server