SQLSERVER Archives - 7 중 2 번째 페이지

[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;

[DATABASE/SQLSERVER] FK 삭제문 만들기

■ FK 삭제문을 만드는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT DISTINCT
    'ALTER TABLE ' +
    '[' + OBJECT_NAME(FK.parent_object_id) + ']' +
    'DROP CONSTRAINT ' +
    '[' + FK.name + ']'
FROM       sys.foreign_keys        AS FK
INNER JOIN sys.foreign_key_columns AS FC ON FK.[object_id] = FC.constraint_object_id
WHERE FK.[schema_id] = 1;

SELECT DISTINCT

'ALTER TABLE ' +

'[' + OBJECT_NAME(FK.parent_object_id) + ']' +

'DROP CONSTRAINT ' +

'[' + FK.name + ']'

FROM sys.foreign_keys AS FK

INNER JOIN sys.foreign_key_columns AS FC ON FK.[object_id] = FC.constraint_object_id

WHERE FK.[schema_id] = 1;

[DATABASE/SQLSERVER] FK 생성문 만들기

■ FK 생성문을 만드는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT
    'ALTER TABLE ' + OBJECT_NAME(FK.parent_object_id) + ' ' +
    'ADD CONSTRAINT ' + '[' + FK.name + '] ' +
    'FOREIGN KEY ' + '(' + COL_NAME(FK.parent_object_id, FC.parent_column_id) + ') ' +
    'REFERENCES ' + '[' + TB.name + '] ' + '(' + COL_NAME(FK.referenced_object_id, FC.referenced_column_id) + ')'
FROM       sys.foreign_keys        AS FK
INNER JOIN sys.foreign_key_columns AS FC ON FC.constraint_object_id = FK.[object_id]
INNER JOIN sys.tables              AS TB ON TB.[object_id]          = FK.referenced_object_id;

SELECT

'ALTER TABLE ' + OBJECT_NAME(FK.parent_object_id) + ' ' +

'ADD CONSTRAINT ' + '[' + FK.name + '] ' +

'FOREIGN KEY ' + '(' + COL_NAME(FK.parent_object_id, FC.parent_column_id) + ') ' +

'REFERENCES ' + '[' + TB.name + '] ' + '(' + COL_NAME(FK.referenced_object_id, FC.referenced_column_id) + ')'

FROM sys.foreign_keys AS FK

INNER JOIN sys.foreign_key_columns AS FC ON FC.constraint_object_id = FK.[object_id]

INNER JOIN sys.tables AS TB ON TB.[object_id] = FK.referenced_object_id;

[DATABASE/SQLSERVER] CROSS JOIN 키워드 : 크로스 조인하기

■ CROSS JOIN 키워드를 사용해 크로스 조인을 하는 방법을 보여준다. ▶ 예제 코드 (SQL)


-- A 테이블을 생성한다.
CREATE TABLE A
(
    VAL INT
)

INSERT A VALUES (1);
INSERT A VALUES (2);
INSERT A VALUES (3);

-- B 테이블을 생성한다.
CREATE TABLE B
(
    VAL INT
)

INSERT B VALUES (1);
INSERT B VALUES (2);

-- A 테이블과B 테이블을 크로스 조인한다.
SELECT
    A.VAL AS AVAL
   ,B.VAL AS BVAL
FROM       A
CROSS JOIN B;

-- 테스트 테이블을 삭제한다.
DROP TABLE A;
DROP TABLE B;

-- A 테이블을 생성한다.

CREATE TABLE A

(

VAL INT

)

INSERT A VALUES (1);

INSERT A VALUES (2);

INSERT A VALUES (3);

-- B 테이블을 생성한다.

CREATE TABLE B

(

VAL INT

)

INSERT B VALUES (1);

INSERT B VALUES (2);

-- A 테이블과B 테이블을 크로스 조인한다.

SELECT

A.VAL AS AVAL

,B.VAL AS BVAL

FROM A

CROSS JOIN B;

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

DROP TABLE A;

DROP TABLE B;

[DATABASE/SQLSERVER] 저장 프로시저 참조 테이블 및 컬럼 정보 조회하기

■ 저장 프로시저 참조 테이블 및 컬럼 정보를 조회하는 방법을 보여준다. ▶ 예제 코드 (SQL)


EXECUTE sp_depends 'dbo.저장프로시저명'

EXECUTE sp_depends 'dbo.저장프로시저명'

[DATABASE/SQLSERVER] MERGE문을 사용해 타겟 테이블에 소스 테이블 추가 또는 수정하기

■ MERGE 명령을 사용해 타겟 테이블에 소스 테이블의 데이터를 추가하거나 소스 테이블의 데이터로 수정하는 방법을 보여준다. ▶ 예제 코드 (SQL)


-- 타겟 테이블을 생성한다.
CREATE TABLE dbo.TargetTable
(
    UserID    VARCHAR(20)
   ,Name      VARCHAR(10)
   ,Phone     CHAR(13)
   ,[Address] VARCHAR(100)
   ,CONSTRAINT PKTargetTable PRIMARY KEY (UserID)
)
GO

INSERT INTO dbo.TargetTable VALUES ('joo'   , '주형권' , '010-1234-5678', '경기도고양시덕양구화정동'  );
INSERT INTO dbo.TargetTable VALUES ('hyoung', '주형진' , '010-1111-5678', '경기도고양시덕양구행신동'  );
INSERT INTO dbo.TargetTable VALUES ('kwon'  , '지순복' , '010-5678-5678', '경기도고양시덕양구일산동구');

--  소스 테이블을 생성한다.
CREATE TABLE dbo.SourceTable
(
    UserID    VARCHAR(20)
   ,Name      VARCHAR(10)
   ,Phone     CHAR(13)
   ,[Address] VARCHAR(100)
   ,CONSTRAINT PKSourceTable PRIMARY KEY (UserID)
)
GO

INSERT INTO dbo.SourceTable VALUES ('kim', '김말똥', '010-5234-5678', '서울시서초구');
INSERT INTO dbo.SourceTable VALUES ('joo', '주형권', '010-8888-5678', '서울시강남구');

-- 병합 전 타겟 테이블을 출력한다.
SELECT * FROM dbo.TargetTable;

-- 타겟 테이블에 소스 테이블을 병합한다.
MERGE TargetTable AS A
USING SourceTable AS B ON B.UserID = A.UserID
WHEN MATCHED THEN
    UPDATE
    SET
        A.Name      = B.Name
       ,A.Phone     = B.Phone
       ,A.[Address] = B.[Address]
WHEN NOT MATCHED THEN
    INSERT
    (
        UserID
       ,Name
       ,Phone
       ,[Address]
    )
    VALUES
    (
        B.UserID
       ,B.Name
       ,B.Phone
       ,B.[Address]
    );

-- 병합 후 타겟 테이블을 출력한다.
SELECT * FROM dbo.TargetTable;

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

-- 타겟 테이블을 생성한다.

CREATE TABLE dbo.TargetTable

(

UserID VARCHAR(20)

,Name VARCHAR(10)

,Phone CHAR(13)

,[Address] VARCHAR(100)

,CONSTRAINT PKTargetTable PRIMARY KEY (UserID)

)

INSERT INTO dbo.TargetTable VALUES ('joo' , '주형권' , '010-1234-5678', '경기도고양시덕양구화정동' );

INSERT INTO dbo.TargetTable VALUES ('hyoung', '주형진' , '010-1111-5678', '경기도고양시덕양구행신동' );

INSERT INTO dbo.TargetTable VALUES ('kwon' , '지순복' , '010-5678-5678', '경기도고양시덕양구일산동구');

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

CREATE TABLE dbo.SourceTable

(

UserID VARCHAR(20)

,Name VARCHAR(10)

,Phone CHAR(13)

,[Address] VARCHAR(100)

,CONSTRAINT PKSourceTable PRIMARY KEY (UserID)

)

INSERT INTO dbo.SourceTable VALUES ('kim', '김말똥', '010-5234-5678', '서울시서초구');

INSERT INTO dbo.SourceTable VALUES ('joo', '주형권', '010-8888-5678', '서울시강남구');

-- 병합 전 타겟 테이블을 출력한다.

SELECT * FROM dbo.TargetTable;

-- 타겟 테이블에 소스 테이블을 병합한다.

MERGE TargetTable AS A

USING SourceTable AS B ON B.UserID = A.UserID

WHEN MATCHED THEN

UPDATE

SET

A.Name = B.Name

,A.Phone = B.Phone

,A.[Address] = B.[Address]

WHEN NOT MATCHED THEN

INSERT

(

UserID

,Name

,Phone

,[Address]

)

VALUES

(

B.UserID

,B.Name

,B.Phone

,B.[Address]

);

-- 병합 후 타겟 테이블을 출력한다.

SELECT * FROM dbo.TargetTable;

-- 테이블을 삭제한다.

DROP TABLE dbo.TargetTable;

DROP TABLE dbo.SourceTable;

[DATABASE/SQLSERVER] 스키마 구하기

■ 스키마를 구하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT USER_NAME() AS SchemaName;

SELECT USER_NAME() AS SchemaName;

[DATABASE/SQLSERVER] 로그인 계정 구하기

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


SELECT
    SUSER_SNAME() AS LoginName
   ,SUSER_SID()   AS LoginSID;

SELECT

SUSER_SNAME() AS LoginName

,SUSER_SID() AS LoginSID;

[DATABASE/SQLSERVER] DATEPART 함수 사용하기

■ DATEPART 함수를 사용하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT DATEPART(DD, GETDATE()); -- 일자
SELECT DATEPART(MM, GETDATE()); -- 월
SELECT DATEPART(YY, GETDATE()); -- 연도
SELECT DATEPART(WW, GETDATE()); -- 주차
SELECT DATEPART(DY, GETDATE()); -- 연중 일 수

SELECT DATEPART(DD, GETDATE()); -- 일자

SELECT DATEPART(MM, GETDATE()); -- 월

SELECT DATEPART(YY, GETDATE()); -- 연도

SELECT DATEPART(WW, GETDATE()); -- 주차

SELECT DATEPART(DY, GETDATE()); -- 연중 일 수

[DATABASE/SQLSERVER] DATEADD 함수 사용하기

■ DATEADD 함수를 사용하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT DATEADD(DD, 10, GETDATE()); -- 10일 추가
SELECT DATEADD(MM, 10, GETDATE()); -- 10개월 추가
SELECT DATEADD(YY, 10, GETDATE()); -- 10년 추가
SELECT DATEADD(WW, 10, GETDATE()); -- 10주 추가

SELECT DATEADD(DD, 10, GETDATE()); -- 10일 추가

SELECT DATEADD(MM, 10, GETDATE()); -- 10개월 추가

SELECT DATEADD(YY, 10, GETDATE()); -- 10년 추가

SELECT DATEADD(WW, 10, GETDATE()); -- 10주 추가

[DATABASE/SQLSERVER] DATEDIFF 함수 사용하기

■ DATEDIFF 함수를 사용하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT DATEDIFF(DD, '20050101', GETDATE()); -- 일차
SELECT DATEDIFF(MM, '20050101', GETDATE()); -- 월차
SELECT DATEDIFF(YY, '20050101', GETDATE()); -- 년차
SELECT DATEDIFF(WW, '20050101', GETDATE()); -- 주차

SELECT DATEDIFF(DD, '20050101', GETDATE()); -- 일차

SELECT DATEDIFF(MM, '20050101', GETDATE()); -- 월차

SELECT DATEDIFF(YY, '20050101', GETDATE()); -- 년차

SELECT DATEDIFF(WW, '20050101', GETDATE()); -- 주차

[DATABASE/SQLSERVER] SPID로 마지막 실행 SQL문 조회하기

■ SPID로 마지막 실행 SQL문을 조회하는 방법을 보여준다. ▶ 예제 코드 (SQL)


DBCC INPUTBUFFER(9); -- SPID

DBCC INPUTBUFFER(9); -- SPID