SQLSERVER Archives - 8 중 2 번째 페이지

[DATABASE/SQLSERVER] CTE 재귀 호출 사용하기

■ CTE 재귀 호출을 사용하는 방법을 보여준다. ▶ 예제 코드 (SQL)


CREATE TABLE MENU
(
    ID        INT
   ,PARENT_ID INT
   ,NAME      VARCHAR(50)
);

INSERT INTO MENU VALUES (1, 0, '메뉴');
INSERT INTO MENU VALUES (2, 0, '메뉴');
INSERT INTO MENU VALUES (3, 1, '메뉴');
INSERT INTO MENU VALUES (4, 1, '메뉴');
INSERT INTO MENU VALUES (5, 1, '메뉴');
INSERT INTO MENU VALUES (6, 5, '메뉴');
INSERT INTO MENU VALUES (7, 2, '메뉴');
INSERT INTO MENU VALUES (8, 2, '메뉴');

WITH CTE(ID, PARENT_ID, NAME, DISPLAY_ORDER, TREE_PATH)
AS
(
    SELECT
        ID
       ,PARENT_ID
       ,NAME
       ,CONVERT(VARCHAR(4000), ID  ) AS DISPLAY_ORDER
       ,CONVERT(VARCHAR(4000), NAME) AS TREE_PATH
    FROM MENU
    WHERE PARENT_ID = 0
    UNION ALL
    SELECT
        A.ID
       ,A.PARENT_ID
       ,A.NAME
       ,CONVERT(VARCHAR(4000), B.DISPLAY_ORDER + ' > ' + CONVERT(VARCHAR(4000), A.ID)) AS DISPLAY_ORDER
       ,CONVERT(VARCHAR(4000), B.TREE_PATH     + ' > ' + A.NAME                      ) AS TREE_PATH
    FROM  MENU AS A, CTE AS B
    WHERE A.PARENT_ID = B.ID
)
SELECT
    NAME
   ,TREE_PATH
FROM CTE
ORDER BY DISPLAY_ORDER;

CREATE TABLE MENU

(

ID INT

,PARENT_ID INT

,NAME VARCHAR(50)

);

INSERT INTO MENU VALUES (1, 0, '메뉴');

INSERT INTO MENU VALUES (2, 0, '메뉴');

INSERT INTO MENU VALUES (3, 1, '메뉴');

INSERT INTO MENU VALUES (4, 1, '메뉴');

INSERT INTO MENU VALUES (5, 1, '메뉴');

INSERT INTO MENU VALUES (6, 5, '메뉴');

INSERT INTO MENU VALUES (7, 2, '메뉴');

INSERT INTO MENU VALUES (8, 2, '메뉴');

WITH CTE(ID, PARENT_ID, NAME, DISPLAY_ORDER, TREE_PATH)

(

SELECT

,PARENT_ID

,NAME

,CONVERT(VARCHAR(4000), ID ) AS DISPLAY_ORDER

,CONVERT(VARCHAR(4000), NAME) AS TREE_PATH

FROM MENU

WHERE PARENT_ID = 0

UNION ALL

SELECT

A.ID

,A.PARENT_ID

,A.NAME

,CONVERT(VARCHAR(4000), B.DISPLAY_ORDER + ' > ' + CONVERT(VARCHAR(4000), A.ID)) AS DISPLAY_ORDER

,CONVERT(VARCHAR(4000), B.TREE_PATH + ' > ' + A.NAME ) AS TREE_PATH

FROM MENU AS A, CTE AS B

WHERE A.PARENT_ID = B.ID

)

SELECT

NAME

,TREE_PATH

FROM CTE

ORDER BY DISPLAY_ORDER;

[DATABASE/SQLSERVER] CTE 재귀 호출 사용하기

■ CTE 재귀 호출을 사용하는 방법을 보여준다. ▶ 예제 코드 (SQL)


WITH CTE(ParentID, ID, Name, DisplayOrder, MenuLevel)
AS
(
    SELECT
        ----------------------------------------
        A.ParentID
       ,A.ID
       ,A.Name
       ,A.DisplayOrder
       ----------------------------------------
       ,1 AS MenuLevel
    FROM Menu AS A
    WHERE A.ParentID = -1
    UNION ALL
    SELECT
        ----------------------------------------
        A.ParentID
       ,A.ID
       ,A.Name
       ,A.DisplayOrder
        ----------------------------------------
       ,B.MenuLevel + 1
    FROM Menu AS A, CTE AS B
    WHERE A.ParentID = B.ID
)
SELECT ParentID, ID, Name, DisplayOrder FROM CTE
ORDER BY MenuLevel;

WITH CTE(ParentID, ID, Name, DisplayOrder, MenuLevel)

(

SELECT

----------------------------------------

A.ParentID

,A.ID

,A.Name

,A.DisplayOrder

----------------------------------------

,1 AS MenuLevel

FROM Menu AS A

WHERE A.ParentID = -1

UNION ALL

SELECT

----------------------------------------

A.ParentID

,A.ID

,A.Name

,A.DisplayOrder

----------------------------------------

,B.MenuLevel + 1

FROM Menu AS A, CTE AS B

WHERE A.ParentID = B.ID

)

SELECT ParentID, ID, Name, DisplayOrder FROM CTE

ORDER BY MenuLevel;

[DATABASE/SQLSERVER] 저장 프로시저 소스 코드 조회하기

■ 저장 프로시저의 소스 코드를 조회하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT
    A.name         AS 저장프로시저
   ,B.[definition] AS 소스코드
FROM  sys.objects     AS A
JOIN  sys.sql_modules AS B ON B.object_id = A.object_id
WHERE A.[type] = 'P';

SELECT

A.name AS 저장프로시저

,B.[definition] AS 소스코드

FROM sys.objects AS A

JOIN sys.sql_modules AS B ON B.object_id = A.object_id

WHERE A.[type] = 'P';

[DATABASE/SQLSERVER] 데이터베이스 내 모든 테이블 모든 행 삭제하기

■ 데이터베이스 내 모든 테이블의 모든 행을 삭제하는 방법을 보여준다. ▶ 예제 코드 (SQL)


DECLARE TableCursor CURSOR
FOR SELECT name FROM dbo.sysobjects WHERE type = 'U';

OPEN TableCursor;

DECLARE @TableName sysname;

FETCH NEXT FROM TableCursor INTO @TableName;

WHILE (@@FETCH_STATUS <> -1)
BEGIN
	EXECUTE ('DELETE ' + @TableName);

	FETCH NEXT FROM TableCursor INTO @TableName;
END;

DEALLOCATE TableCursor;

DECLARE TableCursor CURSOR

FOR SELECT name FROM dbo.sysobjects WHERE type = 'U';

OPEN TableCursor;

DECLARE @TableName sysname;

FETCH NEXT FROM TableCursor INTO @TableName;

WHILE (@@FETCH_STATUS <> -1)

BEGIN

EXECUTE ('DELETE ' + @TableName);

FETCH NEXT FROM TableCursor INTO @TableName;

END;

DEALLOCATE TableCursor;

※ 주제의 특성상 테스트를 할 수 없었다.

[DATABASE/SQLSERVER] 데이터베이스 내 모든 테이블 제거하기

■ 데이터베이스 내 모든 테이블을 제거하는 방법을 보여준다. ▶ 예제 코드 (SQL)


DECLARE TableCursor CURSOR
FOR SELECT name FROM dbo.sysobjects WHERE type = 'U';

OPEN TableCursor;

DECLARE @TableName sysname;

FETCH NEXT FROM TableCursor INTO @TableName;

WHILE (@@FETCH_STATUS <> -1)
BEGIN
    EXECUTE ('DROP TABLE ' + @TableName);

    FETCH NEXT FROM TableCursor INTO @TableName;
END;

DEALLOCATE TableCursor;

DECLARE TableCursor CURSOR

FOR SELECT name FROM dbo.sysobjects WHERE type = 'U';

OPEN TableCursor;

DECLARE @TableName sysname;

FETCH NEXT FROM TableCursor INTO @TableName;

WHILE (@@FETCH_STATUS <> -1)

BEGIN

EXECUTE ('DROP TABLE ' + @TableName);

FETCH NEXT FROM TableCursor INTO @TableName;

END;

DEALLOCATE TableCursor;

※ 주제의 특성상 테스트를 할 수 없었다.

[DATABASE/SQLSERVER] 테이블별 사용 용량 구하기

■ 테이블별 사용 용량을 구하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT
    MAX(B.name)                                                  AS TableName
   ,CAST((SUM(A.reserved) * 8192.0 / 1024   ) AS NUMERIC(18, 0)) AS TableSizeKB
   ,CAST((SUM(A.reserved) * 8192.0 / 1048576) AS NUMERIC(18, 0)) AS TableSizeMB
FROM       sysindexes A
INNER JOIN sysobjects B ON B.id = A.id
WHERE A.indid in (0, 1, 255)
AND   B.xtype = 'U'
GROUP BY A.id
ORDER BY SUM(a.reserved) DESC;

SELECT

MAX(B.name) AS TableName

,CAST((SUM(A.reserved) * 8192.0 / 1024 ) AS NUMERIC(18, 0)) AS TableSizeKB

,CAST((SUM(A.reserved) * 8192.0 / 1048576) AS NUMERIC(18, 0)) AS TableSizeMB

FROM sysindexes A

INNER JOIN sysobjects B ON B.id = A.id

WHERE A.indid in (0, 1, 255)

AND B.xtype = 'U'

GROUP BY A.id

ORDER BY SUM(a.reserved) DESC;

[DATABASE/SQLSERVER] 테이블별 행 수 구하기

■ 테이블별 행 수를 구하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT
    B.name AS TableName
   ,A.rows AS [RowCount]
FROM dbo.sysindexes A INNER JOIN dbo.sysobjects B ON B.id = A.id
WHERE A.indid < 2
AND   B.xtype = 'U'
ORDER BY B.name;

SELECT

B.name AS TableName

,A.rows AS [RowCount]

FROM dbo.sysindexes A INNER JOIN dbo.sysobjects B ON B.id = A.id

WHERE A.indid < 2

AND B.xtype = 'U'

ORDER BY B.name;

[DATABASE/SQLSERVER] SQL Server 설치시 1433 포트가 열리지 않은 경우 처리하기

■ SQL Server 설치시 1433 포트가 열리지 않은 경우를 처리하는 방법을 보여준다. SQL Server 2008로 서비스를 실행했는데도 netstat -na에서 1433 포트와 관련된

[DATABASE/SQLSERVER] 영어 버전 윈도우즈 및 SQL Server 설치시 한글 사용하기

■ 영어 버전 윈도우즈 및 SQL Server 설치시 한글을 사용하는 방법을 보여준다. Database 생성시 Collation을 'Korean_Wansung_CI_AI'로 설정한다.

[DATABASE/SQLSERVER] 조회 컬럼 순서를 지정해 ORDER BY절 사용하기

■ 조회 컬럼 순서를 지정해 ORDER BY절을 사용하는 방법을 보여준다. ▶ 예제 코드 (SQL)


[컬럼명 지정시]

SELECT
    MenuID
   ,ID
   ,Name
   ,CategoryName
FROM  PhotoBook
WHERE MenuID = 46
ORDER BY CategoryName;

[컬럼 순서 지정시]

SELECT
    MenuID
   ,ID
   ,Name
   ,CategoryName
FROM  PhotoBook
WHERE MenuID = 46
ORDER BY 4;

[컬럼명 지정시]

SELECT

MenuID

,ID

,Name

,CategoryName

FROM PhotoBook

WHERE MenuID = 46

ORDER BY CategoryName;

[컬럼 순서 지정시]

SELECT

MenuID

,ID

,Name

,CategoryName

FROM PhotoBook

WHERE MenuID = 46

ORDER BY 4;

※ 상기 쿼리 모두 동일한 결과를 갖는다.

[DATABASE/SQLSERVER] 특정일 해당 월 첫번째 날짜 구하기

■ 특정일의 해당 월 첫번째 날짜를 구하는 방법을 보여준다. ▶ 예제 코드 (SQL)


DECLARE @Date DATETIME;

SET @Date = GETDATE();

SELECT DATEADD(D, -DAY(@Date - 1), @Date);

DECLARE @Date DATETIME;

SET @Date = GETDATE();

SELECT DATEADD(D, -DAY(@Date - 1), @Date);

※ 기존 등록된 '특정일 해당 월 첫번째 날짜

[DATABASE/SQLSERVER] 특정일 해당 월 마지막 날짜 구하기

■ 특정일의 해당 월 마지막 날짜를 구하는 방법을 보여준다. ▶ 예제 코드 (SQL)


DECLARE @Date DATETIME;

SET @Date = GETDATE();

SELECT DATEADD(MONTH, 1, @Date) - DAY(@Date);

DECLARE @Date DATETIME;

SET @Date = GETDATE();

SELECT DATEADD(MONTH, 1, @Date) - DAY(@Date);

※ 기존 등록된 '특정일 해당 월 마지막 날짜

[DATABASE/SQLSERVER] 로그 파일 지우기

■ 로그 파일을 지우는 방법을 보여준다. ▶ 예제 코드 (SQL)


BACKUP LOG Voyager WITH NO_LOG;

DBCC SHRINKFILE(VoyagerLog, 2);

BACKUP LOG Voyager WITH NO_LOG;

DBCC SHRINKFILE(VoyagerLog, 2);

※ Voyager : 데이터베이스명 ※ VoyagerLog : 데이터베이스 로그명

[DATABASE/SQLSERVER] 특정일 해당 월 주차 구하기

■ 특정일의 해당 월 주차를 구하는 방법을 보여준다. ▶ 예제 코드 (SQL)


DECLARE @Date DATETIME;

SET @Date = GETDATE();

SELECT CEILING((DAY(@Date) + DATEPART(DW, LEFT(CONVERT(VARCHAR(10), @Date, 112), 6) + '01') - 1) / 7.0);

DECLARE @Date DATETIME;

SET @Date = GETDATE();

SELECT CEILING((DAY(@Date) + DATEPART(DW, LEFT(CONVERT(VARCHAR(10), @Date, 112), 6) + '01') - 1) / 7.0);

[DATABASE/SQLSERVER] 트랜잭션 로그 지우기

■ 트랜잭션 로그를 지우는 방법을 보여준다. ▶ 예제 코드 (SQL)


BACKUP LOG Voyager WITH NO_LOG;

DBCC SHRINKFILE (VoyagerLog, TRUNCATEONLY);

BACKUP LOG Voyager WITH NO_LOG;

DBCC SHRINKFILE (VoyagerLog, TRUNCATEONLY);

※ Voyager : 데이터베이스명 ※ VoyagerLog : 데이터베이스 로그명

[DATABASE/SQLSERVER] 컬럼 주석 추가하기

■ 컬럼 주석을 추가하는 방법을 보여준다. ▶ 예제 코드 (SQL)


EXECUTE sp_addextendedproperty 'MS_Description', '메뉴 ID', 'user', dbo, 'table', PhotoBook, 'column', MenuID;

EXECUTE sp_addextendedproperty 'MS_Description', '메뉴 ID', 'user', dbo, 'table', PhotoBook, 'column', MenuID;

[DATABASE/SQLSERVER] 테이블 주석 추가하기

■ 테이블 주석을 추가하는 방법을 보여준다. ▶ 예제 코드 (SQL)


EXECUTE sp_addextendedproperty 'MS_Description', '사진집', 'user', dbo, 'table', PhotoBook;

EXECUTE sp_addextendedproperty 'MS_Description', '사진집', 'user', dbo, 'table', PhotoBook;

[DATABASE/SQLSERVER] 타임 스탬프 문자열 구하기

■ 타임 스탬프 문자열을 구하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT FORMAT(CURRENT_TIMESTAMP, 'yyyyMMdd hhmmss ffff');

SELECT FORMAT(CURRENT_TIMESTAMP, 'yyyyMMdd hhmmss ffff');

※ 버전 2012에서 실행된다.

[DATABASE/SQLSERVER] 컬럼 타입 변경하기

■ 컬럼 타입을 변경하는 방법을 보여준다. ▶ 예제 코드 (SQL)


ALTER TABLE dbo.CashBook ALTER COLUMN Name NVARCHAR(300);

ALTER TABLE dbo.CashBook ALTER COLUMN Name NVARCHAR(300);

※ CashBook 테이블 Name 컬럼의 데이터 타입을 NVARCHAR(300)으로 변경한다.

[DATABASE/SQLSERVER] 테이블명 변경하기

■ 테이블명을 변경하는 방법을 보여준다. ▶ 예제 코드 (SQL)


EXECUTE sp_rename 'CashBook', 'NewCashBook';

EXECUTE sp_rename 'CashBook', 'NewCashBook';

※ CashBook 테이블명을 NewCashBook으로 변경한다.

[DATABASE/SQLSERVER] 컬럼명 변경하기

■ 컬럼명을 변경하는 방법을 보여준다. ▶ 예제 코드 (SQL)


EXECUTE sp_rename 'CachBook.ID', 'CachBookID', 'COLUMN';

EXECUTE sp_rename 'CachBook.ID', 'CachBookID', 'COLUMN';

※ CashBook 테이블의 ID 컬럼명을 CashBookID로 변경한다.

[DATABASE/SQLSERVER] NTILE 함수를 사용해 그룹 나누기

■ NTILE 함수를 사용해 그룹을 나누는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT
    NTILE(4) OVER(ORDER BY ID) AS GroupID
   ,A.*
FROM dbo.Code AS A
ORDER BY A.ID ASC;

/*
GroupID ID Name
------- -- -------------------
1       1  예/아니오
1       2  아이콘종류
1       3  성별
1       4  전화부관계
1       5  요일
2       6  화폐종류
2       7  광물종류
2       8  사진집정렬종류
2       9  폰트집정렬종류
3       10 상태종류
3       11 요약종류
3       12 현금출납부대분류
3       13 현금출납부구분
4       14 NULL 여부
4       15 슬라이드쇼방향
4       16 설치여부
4       17 텍스트종류
*/

SELECT

NTILE(4) OVER(ORDER BY ID) AS GroupID

,A.*

FROM dbo.Code AS A

ORDER BY A.ID ASC;

GroupID ID Name

------- -- -------------------

1 1 예/아니오

1 2 아이콘종류

1 3 성별

1 4 전화부관계

1 5 요일

2 6 화폐종류

2 7 광물종류

2 8 사진집정렬종류

2 9 폰트집정렬종류

3 10 상태종류

3 11 요약종류

3 12 현금출납부대분류

3 13 현금출납부구분

4 14 NULL 여부

4 15 슬라이드쇼방향

4 16 설치여부

4 17 텍스트종류

[DATABASE/SQLSERVER] 로그 조회하기

■ 로그를 조회하는 방법을 보여준다. ▶ 예제 코드 (SQL)


EXECUTE master.dbo.xp_readerrorlog;

EXECUTE master.dbo.xp_readerrorlog;

[DATABASE/SQLSERVER] COLLATE 키워드 : 대소문자 구분하기

■ COLLATE 키워드를 사용해 대소문자를 구분하는 방법을 보여준다. ▶ 예제 코드 (SQL)


-- 소스 테이블을 생성한다.
CREATE TABLE dbo.SourceTable
(
    UserID VARCHAR(30) COLLATE KOREAN_WANSUNG_CS_AS
)
GO

INSERT INTO dbo.SourceTable VALUES ('KKKK');
INSERT INTO dbo.SourceTable VALUES ('kkkk');

-- 소문자만 조회한다.
SELECT * FROM dbo.SourceTable AS A WHERE A.UserID = 'kkkk';

-- 테스트 테이블을 삭제한다.
DROP TABLE dbo.SourceTable;

-- 소스 테이블을 생성한다.

CREATE TABLE dbo.SourceTable

(

UserID VARCHAR(30) COLLATE KOREAN_WANSUNG_CS_AS

)

INSERT INTO dbo.SourceTable VALUES ('KKKK');

INSERT INTO dbo.SourceTable VALUES ('kkkk');

-- 소문자만 조회한다.

SELECT * FROM dbo.SourceTable AS A WHERE A.UserID = 'kkkk';

-- 테스트 테이블을 삭제한다.

DROP TABLE dbo.SourceTable;

[DATABASE/SQLSERVER] 로그 정리하기

■ 로그를 정리하는 방법을 보여준다. ▶ 예제 코드 (SQL)


EXECUTE sp_cycle_errorlog;

EXECUTE sp_cycle_errorlog;