개발굼

-- 인덱스 조각화 조회

SELECT

            distinct OBJECT_NAME(S.OBJECT_ID) AS 'TableName'

          , I.NAME AS 'IndexName'

          , ROUND(AVG_FRAGMENTATION_IN_PERCENT, 2) AS 'Fragmentation %'

 FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(),NULL,NULL,NULL,NULL) AS S

 INNER JOIN SYS.INDEXES AS I

 ON S.OBJECT_ID = I.OBJECT_ID AND S.INDEX_ID = I.INDEX_ID

 WHERE S.DATABASE_ID = DB_ID() 

 AND I.NAME IS NOT NULL -- HEAP은 무시

 AND OBJECTPROPERTY(S.OBJECT_ID, 'IsMsShipped') = 0 -- 시스템 개체 무시

 and ROUND(AVG_FRAGMENTATION_IN_PERCENT, 2) > 10

-- 조각화 테이블에 대한 인덱스 리빌드 및 통계 업데이트.

set quoted_identifier  OFF

DECLARE @tablename varchar(255)

DECLARE @tablename_header varchar(255)

declare @sql varchar(1000)

DECLARE tnames_cursor CURSOR FOR SELECT

            distinct OBJECT_NAME(S.OBJECT_ID) AS 'TableName'

--          , I.NAME AS 'IndexName'

--          , ROUND(AVG_FRAGMENTATION_IN_PERCENT, 2) AS 'Fragmentation %'

 FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(),NULL,NULL,NULL,NULL) AS S

 INNER JOIN SYS.INDEXES AS I

 ON S.OBJECT_ID = I.OBJECT_ID AND S.INDEX_ID = I.INDEX_ID

 WHERE S.DATABASE_ID = DB_ID() -- 현재 데이터베이스

 AND I.NAME IS NOT NULL -- HEAP은 무시

 AND OBJECTPROPERTY(S.OBJECT_ID, 'IsMsShipped') = 0 -- 시스템 개체 무시

 and ROUND(AVG_FRAGMENTATION_IN_PERCENT, 2) > 10

OPEN tnames_cursor

FETCH NEXT FROM tnames_cursor INTO @tablename

WHILE (@@fetch_status <> -1)



    BEGIN

     IF (@@fetch_status <> -2)





         BEGIN

        SELECT @tablename_header = "Updating Indexes FOR table " + RTRIM(UPPER(@tablename))

        PRINT @tablename_header

  SET @sql = 'ALTER INDEX ALL ON ' + @tablename + ' REBUILD WITH (PAD_INDEX = ON, FILLFACTOR = 90) '

    EXEC (@sql)

  PRINT (@sql)

     END

     FETCH NEXT FROM tnames_cursor INTO @tablename

END

PRINT " "

PRINT " "

SELECT @tablename_header = "************* NO MORE TABLES" +

 " *************"

PRINT @tablename_header

PRINT " "

PRINT "Indexes have been updated FOR ALL tables."

DEALLOCATE tnames_cursor

go

 -- 통계 업데이트

 EXEC sp_updatestats @resample='resample';

GO

set quoted_identifier  ON

결과 로그

리빌드 후 인덱스 조각화 조회

'A '서버 데이터베이스의 데이터를 'B' 서버 데이터베이스로 내보내기

1. 'B'서버 데이터베이스의 테이블 삭제
2. 'A'서버 데이터베이스를 'B'서버 데이터 베이스로 내보내기

1. 'B'서버 데이터베이스의 테이블 삭제 (테스트로 테이블 삭제, 실제 운영 DB에서는 절대 주의 요함)

exec sp_MsForEachTable 'DROP TABLE ?'

-- 특정 테이블 제약 조건 확인
SELECT constraint_schema
     , table_name
     , constraint_name
     , column_name
     , ordinal_position
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
 WHERE table_name = '(테이블 이름)';

제약 조건 삭제 후 다시  exec sp_MsForEachTable 'DROP TABLE ?' 쿼리 실행해서 테이블 삭제

2. 'A'서버 데이터베이스를 'B'서버 데이터 베이스로 내보내기

2-1. (데이터베이스 이름) 우클릭 - 태스크(T) - 데이터 내보내기(X)

2-2. 데이터 원본 선택

- Next - > 데이터 원본 선택 - 데이터 원본(D) : Microsoft OLE DB Driver for SQL Server 선택

- 서버 IP 정보 및 사용자 이름, 비밀번호 입력 후 데이터 베이스 선택

- 연결 테스트 해봄 ( "연결 테스트에 성공했습니다" 팝업 뜨면 OK)

- 확인

2-3. 대상 선택

-  대상(D) :  Microsoft OLE DB Driver for SQL Server 선택- 위 2-2. 데이터 원본 선택과 마찬가지로 IP 정보, 사용자 정보 , 데이터베이스 선택 후 확인

2-4. 테이블 복사 또는 쿼리 지정

2-5. 원본 테이블 및 뷰 선택

2-6. 패키지 저장 및 실행

2-7. 실행

1. 계정 생성

- 보안> 로그인 - 우클릭 - '새 로그인..'

   [페이지 선택 - 일반]

    a. 이름 입력 후 SQL Server 인증(S) 선택 후 암호, 암호 확인 입력

    b. '암호 정책 강제 적용(F)' 체크 해제

   [페이지 선택 - 서버역할]

     a.서버 역할 - public, sysadmin 체크

이후 확인

2. 백업

- 생성한 데이터베이스 우클릭 - 태스트 - 스크립트 생성 

- '개체 선택' 에서 '특정 데이터 베이스 개체 선택( 선택 사항)' 이후 확인

- 스크립팅 옵션 설정 - 스크립트 파일로 저장 - 단일 스크립트 파일(G) 선택 

- '요약' 패스 하고 다음 누르면 아래 이미지처럼 스크립트 저장 완료

3. 불러오기

- 파일(F) - 열기(O) - 파일(F)[ctrl +O] 

- 탐색기에서 위 2.백업 에서 저장한 script 파일 선택

- 아래 이미지처럼 스크립트 파일이 열림

- 실행(F5) 

- 이후 로그창에 에러 뜨는지 확인하면서 대응

MS SQL 컬럼 수정 중..

SQL 오류 (4922): 개체 'XXXXX '은(는) 열 'OOOO'에 종속되어 있습니다.
하나 이상의 개체가 이 열에 액세스하므로  ... 이(가) 실패했습니다.

오류가 발생.

검색 결과 특정 Table의 속성을 변경, 삭제하고자 하는 경우 Column 제약 조건( Constratint)으로 속성 변경이 불가함.

아래 코드 입력하여 제약 조건 확인

EXEC sp_help (테이블 이름); 

ex) EXEC sp_help tpt_menu_type;

ALTER TABLE (테이블 이름) DROP CONSTRAINT (제약 이름);

ALTER TABLE dbo.tpt_menu_type DROP CONSTRINT DF__tpt_menu___MENU___6E6C36D8;

이 후 Table 속성 변경하면 정상적으로 적용됨.

[참고] https://slowmonk.tistory.com/76