SQLSERVER Archives - 8 중 7 번째 페이지

[DATABASE/SQLSERVER] 데이터베이스 스키마 조회하기

■ 데이터베이스 스키마를 조회하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT
    UPPER(A.NAME)      AS [테이블영문명]
   ,B.COLORDER         AS [컬럼순번]
   ,UPPER(B.NAME)      AS [컬럼영문명]
   ,UPPER(C.NAME)      AS [자료형]
   ,B.LENGTH           AS [길이]
   ,ISNULL(B.SCALE, 0) AS [정밀도]
   ,ISNULL
    (
        'PK' +
        CONVERT
        (
            VARCHAR(10),
            (
                SELECT SC.KEYNO
                FROM       SYSINDEXES   AS SA WITH(NOLOCK)
                INNER JOIN SYSOBJECTS   AS SB WITH(NOLOCK) ON  SB.ID    =  SA.ID
                                                           AND SB.XTYPE =  'U'
                                                           AND SB.ID    =  A.ID
                INNER JOIN SYSINDEXKEYS AS SC WITH(NOLOCK) ON  SC.ID    =  SA.ID
                                                           AND SC.INDID =  SA.INDID
                INNER JOIN SYSCOLUMNS   AS SD WITH(NOLOCK) ON  SD.ID    =  SC.ID
                                                           AND SD.COLID =  SC.COLID
                                                           AND SD.COLID =  B.COLID
                                                           AND SD.ID    =  B.ID
                WHERE SA.INDID = 1
            )
        ),
        ''
    )                  AS [PK]
   ,(
        CASE B.ISNULLABLE
            WHEN 0 THEN ''
            WHEN 1 THEN 'NULL'
        END
    )                  AS [NULL]
   ,ISNULL(E.TEXT, '') AS [DEFAULT]
FROM      SYSOBJECTS     AS A WITH(NOLOCK)
LEFT JOIN SYSCOLUMNS     AS B WITH(NOLOCK) ON  B.ID    =  A.ID
LEFT JOIN SYSTYPES       AS C WITH(NOLOCK) ON  C.XTYPE =  B.XTYPE
                                           AND C.NAME  <> 'SYSNAME'
LEFT JOIN SYSCONSTRAINTS AS D WITH(NOLOCK) ON  D.ID    =  A.ID
                                           AND D.COLID =  B.COLID
LEFT JOIN SYSCOMMENTS    AS E WITH(NOLOCK) ON  E.ID    =  D.CONSTID
WHERE A.XTYPE = 'U'
ORDER BY A.NAME     ASC,
         B.COLORDER ASC,
         B.NAME     ASC;

SELECT

UPPER(A.NAME) AS [테이블영문명]

,B.COLORDER AS [컬럼순번]

,UPPER(B.NAME) AS [컬럼영문명]

,UPPER(C.NAME) AS [자료형]

,B.LENGTH AS [길이]

,ISNULL(B.SCALE, 0) AS [정밀도]

,ISNULL

(

'PK' +

CONVERT

(

VARCHAR(10),

(

SELECT SC.KEYNO

FROM SYSINDEXES AS SA WITH(NOLOCK)

INNER JOIN SYSOBJECTS AS SB WITH(NOLOCK) ON SB.ID = SA.ID

AND SB.XTYPE = 'U'

AND SB.ID = A.ID

INNER JOIN SYSINDEXKEYS AS SC WITH(NOLOCK) ON SC.ID = SA.ID

AND SC.INDID = SA.INDID

INNER JOIN SYSCOLUMNS AS SD WITH(NOLOCK) ON SD.ID = SC.ID

AND SD.COLID = SC.COLID

AND SD.COLID = B.COLID

AND SD.ID = B.ID

WHERE SA.INDID = 1

)

) AS [PK]

CASE B.ISNULLABLE

WHEN 0 THEN ''

WHEN 1 THEN 'NULL'

END

) AS [NULL]

,ISNULL(E.TEXT, '') AS [DEFAULT]

FROM SYSOBJECTS AS A WITH(NOLOCK)

LEFT JOIN SYSCOLUMNS AS B WITH(NOLOCK) ON B.ID = A.ID

LEFT JOIN SYSTYPES AS C WITH(NOLOCK) ON C.XTYPE = B.XTYPE

AND C.NAME <> 'SYSNAME'

LEFT JOIN SYSCONSTRAINTS AS D WITH(NOLOCK) ON D.ID = A.ID

AND D.COLID = B.COLID

LEFT JOIN SYSCOMMENTS AS E WITH(NOLOCK) ON E.ID = D.CONSTID

WHERE A.XTYPE = 'U'

ORDER BY A.NAME ASC,

B.COLORDER ASC,

B.NAME ASC;

[DATABASE/SQLSERVER] 문자열에서 테이블 생성하기

■ 문자열에서 테이블을 생성하는 방법을 보여준다. ▶ 문자열에서 테이블 생성하기 예제 (SQL)


SELECT A.*
FROM   dbo.CreateTable('1,2,3,4,5', ',') AS A;

SELECT A.*

FROM dbo.CreateTable('1,2,3,4,5', ',') AS A;

▶ 문자열에서 테이블 생성하기 (SQL)


CREATE FUNCTION dbo.CreateTableFromString
(
    @P_String    NVARCHAR(MAX) -- 문자열
   ,@P_Separator NVARCHAR(10)  -- 분리자
)
RETURNS @Result TABLE(Item NVARCHAR(100))
AS
BEGIN
    DECLARE @Temp   NVARCHAR(MAX);
    DECLARE @Start  INT;
    DECLARE @End    INT;
    DECLARE @Length INT;
    DECLARE @Index  INT;

    SET @Temp  = ' ' + @P_String;
    SET @Start = 1;
    SET @End   = 0;
    SET @Index = 0;

    WHILE(@Start <> 0)
    BEGIN
        SET @End = CHARINDEX(@P_Separator, @Temp, @Start);

        IF @End <> 0
        BEGIN
            SET @Length = @End - @Start;
        END
        ELSE
        BEGIN
            SET @Length = LEN(@Temp) - @Start + 1;
        END;

        INSERT INTO @Result(Item) VALUES (RTRIM(LTRIM(SUBSTRING(@Temp, @Start, @Length))));

        SET @Start = @End + 1;

        IF @End = 0
        BEGIN
            RETURN;
        END;
    END;

    RETURN;
END

CREATE FUNCTION dbo.CreateTableFromString

(

@P_String NVARCHAR(MAX) -- 문자열

,@P_Separator NVARCHAR(10) -- 분리자

)

RETURNS @Result TABLE(Item NVARCHAR(100))

BEGIN

DECLARE @Temp NVARCHAR(MAX);

DECLARE @Start INT;

DECLARE @End INT;

DECLARE @Length INT;

DECLARE @Index INT;

SET @Temp = ' ' + @P_String;

SET @Start = 1;

SET @End = 0;

SET @Index = 0;

WHILE(@Start <> 0)

BEGIN

SET @End = CHARINDEX(@P_Separator, @Temp, @Start);

IF @End <> 0

BEGIN

SET @Length = @End - @Start;

END

ELSE

BEGIN

SET @Length = LEN(@Temp) - @Start + 1;

END;

INSERT INTO @Result(Item) VALUES (RTRIM(LTRIM(SUBSTRING(@Temp, @Start, @Length))));

SET @Start = @End + 1;

IF @End = 0

BEGIN

RETURN;

END;

RETURN;

END

[DATABASE/SQLSERVER] SET ROWCOUNT 명령 사용하기

■ SET ROWCOUNT 명령을 사용하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SET ROWCOUNT 100;

SELECT A.*
FROM   PhotoBook AS A WITH(NOLOCK)
ORDER BY ID ASC;

SET ROWCOUNT 0;

SET ROWCOUNT 100;

SELECT A.*

FROM PhotoBook AS A WITH(NOLOCK)

ORDER BY ID ASC;

SET ROWCOUNT 0;

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

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


SELECT
    ROW_NUMBER() OVER (ORDER BY A.DateTimeWritten DESC, A.DateTimeCreated DESC) AS RowNumber
   ,A.ID              AS ID
   ,A.DateTimeWritten AS DateTimeWritten
   ,A.DateTimeCreated AS DateTimeCreated
   ,A.[FileName]      AS [FileName]
   ,A.FileExtension   AS FileExtension
FROM
(
    SELECT
        A.ID              AS ID
       ,A.DateTimeWritten AS DateTimeWritten
       ,A.DateTimeCreated AS DateTimeCreated
       ,A.[FileName]      AS [FileName]
       ,A.FileExtension   AS FileExtension
    FROM  PhotoBook AS A WITH(NOLOCK)
    WHERE A.MenuID       = 44
    AND   A.CategoryName = '자연'
) AS A;

SELECT

ROW_NUMBER() OVER (ORDER BY A.DateTimeWritten DESC, A.DateTimeCreated DESC) AS RowNumber

,A.ID AS ID

,A.DateTimeWritten AS DateTimeWritten

,A.DateTimeCreated AS DateTimeCreated

,A.[FileName] AS [FileName]

,A.FileExtension AS FileExtension

FROM

(

SELECT

A.ID AS ID

,A.DateTimeWritten AS DateTimeWritten

,A.DateTimeCreated AS DateTimeCreated

,A.[FileName] AS [FileName]

,A.FileExtension AS FileExtension

FROM PhotoBook AS A WITH(NOLOCK)

WHERE A.MenuID = 44

AND A.CategoryName = '자연'

) AS A;

[DATABASE/SQLSERVER] 컬럼 정보 조회하기

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


SELECT A.*
FROM   INFORMATION_SCHEMA.COLUMNS AS A WITH(NOLOCK);

SELECT A.*

FROM INFORMATION_SCHEMA.COLUMNS AS A WITH(NOLOCK);

[DATABASE/SQLSERVER] PIVOT 함수를 이용한 동적 집계하기

■ PIVOT 함수를 이용해 동적 집계를 하는 방법을 보여준다. ▶ PIVOT 함수를 이용한 동적 집계하기 예제 (SQL)


DECLARE @SourceSQL NVARCHAR(MAX);

SET @SourceSQL = '
SELECT
    [Name]                                      AS [Name]
   ,FileExtension                               AS FileExtension -- 예제를 위해 포함시킴
   ,CONVERT(NVARCHAR(4), YEAR(DateTimeWritten)) AS [Year]
   ,CONVERT(BIGINT, 1)                          AS CNT
FROM  PhotoBook WITH(NOLOCK)
WHERE MenuID          =  44
AND   CategoryName    =  ''자연''
AND   DateTimeWritten >= ''2010-01-01''
AND   DateTimeWritten <= ''2014-12-31''
';

EXECUTE PivotAggregate @SourceSQL -- 소스 SQL문       ; 타겟 필드 리스트, 피벗 필드, 값 필드에 명시된 컬럼이 나열되어야 한다.
    ,'Name,FileExtension'         -- 타겟 필드 리스트 ; 2개 이상인 경우 콤마로 구분한다.
    ,'Year'                       -- 피벗 필드
    ,'2010,2011,2012,2013,2014'   -- 피벗 필드
    ,'CNT'                        -- 값 필드
    ,'DESC'                       -- 정렬 순서        ; 'ASC' 또는 'DESC'
    ,10;                          -- TOP 카운트       ; 0인 경우 미적용

DECLARE @SourceSQL NVARCHAR(MAX);

SET @SourceSQL = '

SELECT

[Name] AS [Name]

,FileExtension AS FileExtension -- 예제를 위해 포함시킴

,CONVERT(NVARCHAR(4), YEAR(DateTimeWritten)) AS [Year]

,CONVERT(BIGINT, 1) AS CNT

FROM PhotoBook WITH(NOLOCK)

WHERE MenuID = 44

AND CategoryName = ''자연''

AND DateTimeWritten >= ''2010-01-01''

AND DateTimeWritten <= ''2014-12-31''

EXECUTE PivotAggregate @SourceSQL -- 소스 SQL문 ; 타겟 필드 리스트, 피벗 필드, 값 필드에 명시된 컬럼이 나열되어야 한다.

,'Name,FileExtension' -- 타겟 필드 리스트 ; 2개 이상인 경우 콤마로 구분한다.

,'Year' -- 피벗 필드

,'2010,2011,2012,2013,2014' -- 피벗 필드

,'CNT' -- 값 필드

,'DESC' -- 정렬 순서 ; 'ASC' 또는 'DESC'

,10; -- TOP 카운트 ; 0인 경우 미적용

■ PIVOT 함수를 이용한

[DATABASE/SQLSERVER] GROUP BY-CASE문 : 동적 집계하기

■ GROUP BY-CASE 키워드를 이용해 동적 집계를 하는 방법을 보여준다. ▶ GROUP BY-CASE 키워드 : 동적 집계하기 예제 (SQL)


DECLARE @SourceSQL NVARCHAR(MAX);

SET @SourceSQL = N'
SELECT
    [Name]                                      AS [Name]
   ,FileExtension                               AS FileExtension
   ,CONVERT(NVARCHAR(4), YEAR(DateTimeWritten)) AS [Year]
   ,CONVERT(INT, 1)                             AS CNT
FROM  PhotoBook WITH(NOLOCK)
WHERE MenuID          =  44
AND   CategoryName    =  ''자연''
AND   DateTimeWritten >= ''2006-01-01''
AND   DateTimeWritten <  ''2011-01-01''
';

EXECUTE SQLAggregate @SourceSQL      -- 소스 SQL문       ; 타겟 필드 리스트, 피벗 필드, 값 필드에 명시된 컬럼이 나열되어야 한다.
    ,'Name,FileExtension'            -- 타겟 필드 리스트 ; 2개 이상인 경우 콤마로 구분한다.
    ,'Year'                          -- 피벗 필드
    ,'2006,2007,2008,2009,2010,2011' -- 피벗 값 리스트
    ,'CNT'                           -- 값 필드
    ,'DESC'                          -- 정렬 순서        ; 'ASC' 또는 'DESC'
    ,10;                             -- TOP 카운트       ; 0인 경우 미적용

DECLARE @SourceSQL NVARCHAR(MAX);

SET @SourceSQL = N'

SELECT

[Name] AS [Name]

,FileExtension AS FileExtension

,CONVERT(NVARCHAR(4), YEAR(DateTimeWritten)) AS [Year]

,CONVERT(INT, 1) AS CNT

FROM PhotoBook WITH(NOLOCK)

WHERE MenuID = 44

AND CategoryName = ''자연''

AND DateTimeWritten >= ''2006-01-01''

AND DateTimeWritten < ''2011-01-01''

EXECUTE SQLAggregate @SourceSQL -- 소스 SQL문 ; 타겟 필드 리스트, 피벗 필드, 값 필드에 명시된 컬럼이 나열되어야 한다.

,'Name,FileExtension' -- 타겟 필드 리스트 ; 2개 이상인 경우 콤마로 구분한다.

,'Year' -- 피벗 필드

,'2006,2007,2008,2009,2010,2011' -- 피벗 값 리스트

,'CNT' -- 값 필드

,'DESC' -- 정렬 순서 ; 'ASC' 또는 'DESC'

,10; -- TOP 카운트 ; 0인 경우 미적용

▶ GROUP

[DATABASE/SQLSERVER] 동적 SQL 실행하기

■ 동적으로 SQL을 실행하는 방법을 보여준다. ▶ 예제 코드 (SQL)


DECLARE @SQL NVARCHAR(MAX);

SET @SQL = 'SELECT A.* FROM DataBook AS A WITH(NOLOCK) WHERE ID = @EID AND Name = @EName';

EXECUTE SP_EXECUTESQL @SQL,
    N'@EID INT, @EName NVARCHAR(100)',
    @EID   = @ID,
    @EName = @Name;

DECLARE @SQL NVARCHAR(MAX);

SET @SQL = 'SELECT A.* FROM DataBook AS A WITH(NOLOCK) WHERE ID = @EID AND Name = @EName';

EXECUTE SP_EXECUTESQL @SQL,

N'@EID INT, @EName NVARCHAR(100)',

@EID = @ID,

@EName = @Name;

[C#/COMMON] SQL 서버 연결 문자열 구하기

■ SQL 서버 연결 문자열을 구하는 방법을 보여준다. (운영체제 계정 이용시) ▶ SQL 서버 연결 문자열 구하기 (운영체제 계정 이용시) (C#)


#region SQL 서버 연결 문자열 구하기 - GetSQLServerConnectionString(serverAddress, databaseName)

/// <summary>
/// SQL 서버 연결 문자열 구하기
/// </summary>
/// <param name="serverAddress">서버 주소</param>
/// <param name="databaseName">데이터베이스명</param>
/// <returns>연결 문자열</returns>
public string GetSQLServerConnectionString(string serverAddress, string databaseName)
{
    return string.Format("Server={0};Initial Catalog={1};Integrated Security=True", serverAddress, databaseName);
}

#endregion

#region SQL 서버 연결 문자열 구하기 - GetSQLServerConnectionString(serverAddress, databaseName)

/// <summary>

/// SQL 서버 연결 문자열 구하기

/// </summary>

/// <param name="serverAddress">서버 주소</param>

/// <param name="databaseName">데이터베이스명</param>

/// <returns>연결 문자열</returns>

public string GetSQLServerConnectionString(string serverAddress, string databaseName)

{

return string.Format("Server={0};Initial Catalog={1};Integrated Security=True", serverAddress, databaseName);

}

#endregion

[DATABASE/SQLSERVER] 데이터가 없으면 추가하고 있으면 수정하기 #2

■ 데이터가 없으면 추가하고 있으면 수정하는 방법을 보여준다. ▶ 예제 코드 (SQL)


-- 사용자 카운트 테이블을 생성한다.
CREATE TABLE dbo.UserCount
(
    UserID    VARCHAR(20)
   ,UserCount INT
);

INSERT INTO dbo.UserCount VALUES ('a', 10);

-- 'a' 사용자 ID를 INSERT/UPDATE 한다.
UPDATE dbo.UserCount
SET    UserCount = UserCount + 10
WHERE  UserID = 'a';

IF @@ROWCOUNT = 0
BEGIN
    INSERT INTO dbo.UserCount VALUES ('a', 10);
END

-- 'b' 사용자 ID를 INSERT/UPDATE 한다.
UPDATE dbo.UserCount
SET    UserCount = UserCount + 10
WHERE  UserID = 'b';

IF @@ROWCOUNT = 0
BEGIN
    INSERT INTO dbo.UserCount VALUES ('b', 30);
END

-- 사용자 카운트 테이블을 출력한다.
SELECT * FROM dbo.UserCount;

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

-- 사용자 카운트 테이블을 생성한다.

CREATE TABLE dbo.UserCount

(

UserID VARCHAR(20)

,UserCount INT

);

INSERT INTO dbo.UserCount VALUES ('a', 10);

-- 'a' 사용자 ID를 INSERT/UPDATE 한다.

UPDATE dbo.UserCount

SET UserCount = UserCount + 10

WHERE UserID = 'a';

IF @@ROWCOUNT = 0

BEGIN

INSERT INTO dbo.UserCount VALUES ('a', 10);

END

-- 'b' 사용자 ID를 INSERT/UPDATE 한다.

UPDATE dbo.UserCount

SET UserCount = UserCount + 10

WHERE UserID = 'b';

IF @@ROWCOUNT = 0

BEGIN

INSERT INTO dbo.UserCount VALUES ('b', 30);

END

-- 사용자 카운트 테이블을 출력한다.

SELECT * FROM dbo.UserCount;

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

DROP TABLE dbo.UserCount;

[DATABASE/SQLSERVER] 데이터가 없으면 추가하고 있으면 수정하기 #1

■ 데이터가 없으면 추가하고 있으면 수정하는 방법을 보여준다. ▶ 예제 코드 (SQL)


-- 사용자 카운트 테이블을 생성한다.
CREATE TABLE dbo.UserCount
(
    UserID    VARCHAR(20)
   ,UserCount INT
);

INSERT INTO dbo.UserCount VALUES ('a', 10);

-- 'a' 사용자 ID를 INSERT/UPDATE 한다.
IF EXISTS(SELECT * FROM dbo.UserCount AS A WITH(NOLOCK) WHERE A.UserID = 'a')
BEGIN
    UPDATE dbo.UserCount
    SET    UserCount = UserCount + 10
    WHERE  UserID = 'a';
END
ELSE
BEGIN
    INSERT INTO dbo.UserCount VALUES ('a', 10);
END

-- 'b' 사용자 ID를 INSERT/UPDATE 한다.
IF EXISTS(SELECT * FROM dbo.UserCount AS A WITH(NOLOCK) WHERE A.UserID = 'b')
BEGIN
    UPDATE dbo.UserCount
    SET    UserCount = UserCount + 10
    WHERE  UserID = 'b';
END
ELSE
BEGIN
    INSERT INTO dbo.UserCount VALUES ('b', 30);
END

-- 사용자 카운트 테이블을 출력한다.
SELECT * FROM dbo.UserCount;

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

-- 사용자 카운트 테이블을 생성한다.

CREATE TABLE dbo.UserCount

(

UserID VARCHAR(20)

,UserCount INT

);

INSERT INTO dbo.UserCount VALUES ('a', 10);

-- 'a' 사용자 ID를 INSERT/UPDATE 한다.

IF EXISTS(SELECT * FROM dbo.UserCount AS A WITH(NOLOCK) WHERE A.UserID = 'a')

BEGIN

UPDATE dbo.UserCount

SET UserCount = UserCount + 10

WHERE UserID = 'a';

END

ELSE

BEGIN

INSERT INTO dbo.UserCount VALUES ('a', 10);

END

-- 'b' 사용자 ID를 INSERT/UPDATE 한다.

IF EXISTS(SELECT * FROM dbo.UserCount AS A WITH(NOLOCK) WHERE A.UserID = 'b')

BEGIN

UPDATE dbo.UserCount

SET UserCount = UserCount + 10

WHERE UserID = 'b';

END

ELSE

BEGIN

INSERT INTO dbo.UserCount VALUES ('b', 30);

END

-- 사용자 카운트 테이블을 출력한다.

SELECT * FROM dbo.UserCount;

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

DROP TABLE dbo.UserCount;

[DATABASE/SQLSERVER] 프로그램 설치시 ‘컴퓨터 다시 시작’ 오류

■ 프로그램 설치시 '컴퓨터 다시 시작' 오류를 처리하는 방법을 보여준다. 1. regedit를 실행한다. 2. HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager 키를 찾는다. 3. PendingFileRenameOperations 값을 찾아

[C#/WPF/DEVEXPRESS] TextEdit 엘리먼트 : SQL Server DECIMAL(18, 2) 타입 숫자 입력하기

■ TextEdit 엘리먼트에서 SQL Server DECIMAL(18, 2) 타입 숫자를 입력하는 방법을 보여준다. ▶ 예제 코드 (XAML)


<Grid
    xmlns:s="clr-namespace:System;assembly=mscorlib"
    xmlns:dxe="http://schemas.devexpress.com/winfx/2008/xaml/editors">
    <dxe:TextEdit x:Name="decimalTextEdit"
        Width="230"
        Height="30"
        FontFamily="바탕체"
        FontSize="16"
        HorizontalContentAlignment="Right"
        MaskType="Numeric"
        Mask="###,###,###,###,###,##0.00"
        MaskUseAsDisplayFormat="True">
        <dxe:TextEdit.EditValue>
            <s:Decimal>0</s:Decimal>
        </dxe:TextEdit.EditValue>
    </dxe:TextEdit>
</Grid>

<Grid

xmlns:s="clr-namespace:System;assembly=mscorlib"

xmlns:dxe="http://schemas.devexpress.com/winfx/2008/xaml/editors">

<dxe:TextEdit x:Name="decimalTextEdit"

Width="230"

Height="30"

FontFamily="바탕체"

FontSize="16"

HorizontalContentAlignment="Right"

MaskType="Numeric"

Mask="###,###,###,###,###,##0.00"

MaskUseAsDisplayFormat="True">

<dxe:TextEdit.EditValue>

<s:Decimal>0</s:Decimal>

</dxe:TextEdit.EditValue>

</dxe:TextEdit>

</Grid>

※ 양수/음수에 관계없이 정수 부분은

[DATABASE/SQLSERVER] 잠금 해제하기

■ 잠금을 해제하는 방법을 보여준다. ▶ 잠금 정보를 조회한다 (SQL)


EXECUTE sp_lock;

EXECUTE sp_lock;

▶ 클라이언트가 Microsoft SQL Server 인스턴스로 마지막으로 전송한 문을 표시한다 (SQL)

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

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


SELECT DATEDIFF(ww, '2014-03-11', '2014-03-12');

SELECT DATEDIFF(ww, '2014-03-11', '2014-03-12');

▶ DATEDIFF 함수 사용하기 (SQL)


DATEDIFF(interval, date1, date2);

DATEDIFF(interval, date1, date2);

▶ DATEDIFF 함수

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

■ ERROR_NUMBER 함수의 에러 코드를 보여준다. ▶ 에러 코드


─────────────────────────────────
에러 코드   실행 SQL문  원인
─────  ─────  ─────────────────────
8152        INSERT      지정된 데이터 길이를 초과해 입력하는 경우
2627        INSERT      중복된 PK로 입력하는 경우
─────────────────────────────────

─────────────────────────────────

에러 코드 실행 SQL문 원인

───── ───── ─────────────────────

8152 INSERT 지정된 데이터 길이를 초과해 입력하는 경우

2627 INSERT 중복된 PK로 입력하는 경우

─────────────────────────────────

[DATABASE/SQLSERVER] DATEPART 함수 : 요일 구하기

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


SELECT DATEPART(WEEKDAY, GETDATE() - 1);

SELECT DATEPART(WEEKDAY, GETDATE() - 1);

▶ 금일 (SQL)


SELECT DATEPART(WEEKDAY, GETDATE());

SELECT DATEPART(WEEKDAY, GETDATE());

▶ 내일 (SQL)


SELECT DATEPART(WEEKDAY, GETDATE() + 1);

SELECT DATEPART(WEEKDAY, GETDATE() + 1);

[리턴값] 1 : 일요일

[DATABASE/SQLSERVER] 트랜잭션 로그 삭제하기

■ 트랜잭션 로그를 삭제하는 방법을 보여준다. 1. 로그 파일을 삭제하는 경우 데이타베이스를 단독 모드로 설정해야 한다. ▶ 예제 코드 (SQL)


EXECUTE SP_DBOPTION 'TestDB', 'single user', 'true'

EXECUTE SP_DBOPTION 'TestDB', 'single user', 'true'

[DATABASE/SQLSERVER] 날짜 포맷 사용하기

■ 날짜 포맷을 사용하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT CONVERT(VARCHAR(50), GETDATE(), 1);

SELECT CONVERT(VARCHAR(50), GETDATE(), 1);

[날짜 포맷 번호] 0 : Sep 27 2011 8:53AM 1 :

[DATABASE/SQLSERVER] 디폴트 포트가 아닌 다른 포트로 접속하기

■ 디폴트 포트가 아닌 다른 포트로 접속하는 방법을 보여준다. SQL Server의 디폴트 포트 1433 외에 다른 포트(예 : 3000)로 접속해야 하는 경우,

[DATABASE/SQLSERVER] E 메일 주소 검증하기

■ E 메일 주소를 검증하는 방법을 보여준다. ▶ E 메일 주소 검증하기 예제 (SQL)


SELECT dbo.ValidateEMailAddress('icodebroker@naver.com');

SELECT dbo.ValidateEMailAddress('icodebroker@naver.com');

▶ E 메일 주소 검증하기 (SQL)


CREATE FUNCTION [dbo].[ValidateEMailAddress]
(
    @EMailAddress VARCHAR(255)
)
RETURNS BIT
AS
BEGIN
    RETURN
    (
        SELECT
            CASE
                WHEN @EMailAddress IS NULL                                        THEN 0
                WHEN CHARINDEX(' ', @EMailAddress) <> 0 OR
                     CHARINDEX('/', @EMailAddress) <> 0 OR
                     CHARINDEX(':', @EMailAddress) <> 0 OR
                     CHARINDEX(';', @EMailAddress) <> 0                           THEN 0
                WHEN LEN(@EMailAddress) - 1 <= CHARINDEX('.', @EMailAddress)      THEN 0
                WHEN @EMailAddress LIKE '%@%@%' OR @EMailAddress NOT LIKE '%@%.%' THEN 0
                ELSE                                                                   1
	      END
    );
END
GO

CREATE FUNCTION [dbo].[ValidateEMailAddress]

(

@EMailAddress VARCHAR(255)

)

RETURNS BIT

BEGIN

RETURN

(

SELECT

CASE

WHEN @EMailAddress IS NULL THEN 0

WHEN CHARINDEX(' ', @EMailAddress) <> 0 OR

CHARINDEX('/', @EMailAddress) <> 0 OR

CHARINDEX(':', @EMailAddress) <> 0 OR

CHARINDEX(';', @EMailAddress) <> 0 THEN 0

WHEN LEN(@EMailAddress) - 1 <= CHARINDEX('.', @EMailAddress) THEN 0

WHEN @EMailAddress LIKE '%@%@%' OR @EMailAddress NOT LIKE '%@%.%' THEN 0

ELSE 1

END

);

END

[DATABASE/SQLSERVER] 계층적 테이블 조회하기

■ 계층적 테이블을 조회하는 방법을 보여준다. ▶ 계층적 테이블 조회하기 예제 (SQL)


SELECT * FROM GetTreeTable(18);

SELECT * FROM GetTreeTable(18);

▶ 계층적 테이블 조회하기 (SQL)


CREATE FUNCTION [dbo].[GetTreeTable]
(
    @ParentID INT -- 부모 ID
)
RETURNS @ResultTree TABLE
(
    ID           INT
   ,Name         NVARCHAR(50)
   ,ParentID     INT
   ,TreeLevel    INT
   ,TreePath     VARCHAR(2000)
) AS
BEGIN
    DECLARE @TreeLevel INT;

    SELECT @TreeLevel = 0;

    INSERT INTO @ResultTree
    SELECT
        ----------------------------------------
        ID        = A.ID       -- ID
       ,Name      = A.Name     -- 명칭
       ,ParentID  = A.ParentID -- 부모 ID
        ----------------------------------------
       ,TreeLevel = @TreeLevel
       ,TreePath  = RIGHT('00000000' + CAST(A.ID AS VARCHAR(8)), 8)
    FROM   Menu AS A WITH(NOLOCK)
    WHERE  A.ID = @ParentID;

    WHILE @@ROWCOUNT > 0
    BEGIN
        SELECT @TreeLevel = @TreeLevel + 1;

        INSERT INTO @ResultTree
        SELECT
            ----------------------------------------
            ID        = A.ID       -- ID
           ,Name      = A.Name     -- 명칭
           ,ParentID  = A.ParentID -- 부모ID
            ----------------------------------------
           ,TreeLevel = @TreeLevel
           ,TreePath  = B.TreePath + '/' + RIGHT('00000000' + CAST(A.ID AS VARCHAR(8)), 8)
        FROM       Menu        AS A WITH(NOLOCK)
        INNER JOIN @ResultTree AS B ON B.ID = A.ParentID
        WHERE EXISTS
        (
            SELECT B.*
            FROM   @ResultTree AS B
            WHERE  B.ID = A.ParentID
        )
        AND NOT EXISTS
        (
            SELECT C.*
            FROM   @ResultTree AS C
            WHERE  C.ID = A.ID
        );
    END;

    RETURN;
END
GO

CREATE FUNCTION [dbo].[GetTreeTable]

(

@ParentID INT -- 부모 ID

)

RETURNS @ResultTree TABLE

(

ID INT

,Name NVARCHAR(50)

,ParentID INT

,TreeLevel INT

,TreePath VARCHAR(2000)

) AS

BEGIN

DECLARE @TreeLevel INT;

SELECT @TreeLevel = 0;

INSERT INTO @ResultTree

SELECT

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

ID = A.ID -- ID

,Name = A.Name -- 명칭

,ParentID = A.ParentID -- 부모 ID

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

,TreeLevel = @TreeLevel

,TreePath = RIGHT('00000000' + CAST(A.ID AS VARCHAR(8)), 8)

FROM Menu AS A WITH(NOLOCK)

WHERE A.ID = @ParentID;

WHILE @@ROWCOUNT > 0

BEGIN

SELECT @TreeLevel = @TreeLevel + 1;

INSERT INTO @ResultTree

SELECT

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

ID = A.ID -- ID

,Name = A.Name -- 명칭

,ParentID = A.ParentID -- 부모ID

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

,TreeLevel = @TreeLevel

,TreePath = B.TreePath + '/' + RIGHT('00000000' + CAST(A.ID AS VARCHAR(8)), 8)

FROM Menu AS A WITH(NOLOCK)

INNER JOIN @ResultTree AS B ON B.ID = A.ParentID

WHERE EXISTS

(

SELECT B.*

FROM @ResultTree AS B

WHERE B.ID = A.ParentID

)

AND NOT EXISTS

(

SELECT C.*

FROM @ResultTree AS C

WHERE C.ID = A.ID

);

END;

RETURN;

END

[DATABASE/SQLSERVER] DATETIME → ‘yyyyMMdd’ 문자열 변환하기

■ DATETIME 값을 'yyyyMMdd' 문자열로 변환하는 방법을 보여준다. ▶ 예제 코드 (SQL)


CREATE FUNCTION dbo.GetPackedDate
(
    @Date DATETIME
)
RETURNS NVARCHAR(8)
AS
BEGIN
    DECLARE @PackedDate NVARCHAR(8);
    DECLARE @Year       NVARCHAR(4);
    DECLARE @Month      NVARCHAR(2);
    DECLARE @Day        NVARCHAR(2);

    SET @Year  = CONVERT(NVARCHAR(4), YEAR (@Date));
    SET @Month = CONVERT(NVARCHAR(2), Month(@Date));
    SET @Day   = CONVERT(NVARCHAR(2), Day  (@Date));

    SET @Month = SUBSTRING('0' + @Month, LEN(@Month), 2);
    SET @Day   = SUBSTRING('0' + @Day  , LEN(@Day  ), 2);

    SET @PackedDate = @Year + @Month + @Day;

    RETURN @PackedDate;
END

CREATE FUNCTION dbo.GetPackedDate

(

@Date DATETIME

)

RETURNS NVARCHAR(8)

BEGIN

DECLARE @PackedDate NVARCHAR(8);

DECLARE @Year NVARCHAR(4);

DECLARE @Month NVARCHAR(2);

DECLARE @Day NVARCHAR(2);

SET @Year = CONVERT(NVARCHAR(4), YEAR (@Date));

SET @Month = CONVERT(NVARCHAR(2), Month(@Date));

SET @Day = CONVERT(NVARCHAR(2), Day (@Date));

SET @Month = SUBSTRING('0' + @Month, LEN(@Month), 2);

SET @Day = SUBSTRING('0' + @Day , LEN(@Day ), 2);

SET @PackedDate = @Year + @Month + @Day;

RETURN @PackedDate;

END

[DATABASE/SQLSERVER] 주민등록번호 ‘9999999999999’ 문자열 → ‘999999-9999999’ 문자열 변환하기

■ 주민등록번호 '9999999999999' 문자열을 '999999-9999999' 문자열로 변환하는 방법을 보여준다. ▶ 예제 코드 (SQL)


CREATE FUNCTION dbo.ConvertSecurityCodeString
(
    @Source NVARCHAR(13)
)
RETURNS NVARCHAR(14)
BEGIN
    DECLARE @SourceLength INT;

    SET @SourceLength = LEN(@Source);

    RETURN
        CASE
            WHEN @SourceLength < 7 THEN LTRIM(RTRIM(SUBSTRING(@Source, 1, 6)))
            ELSE                        LTRIM(RTRIM(SUBSTRING(@Source, 1, 6))) + '-' +
                                        SUBSTRING(@Source, 7, 7)
        END;
END

CREATE FUNCTION dbo.ConvertSecurityCodeString

(

@Source NVARCHAR(13)

)

RETURNS NVARCHAR(14)

BEGIN

DECLARE @SourceLength INT;

SET @SourceLength = LEN(@Source);

RETURN

CASE

WHEN @SourceLength < 7 THEN LTRIM(RTRIM(SUBSTRING(@Source, 1, 6)))

ELSE LTRIM(RTRIM(SUBSTRING(@Source, 1, 6))) + '-' +

SUBSTRING(@Source, 7, 7)

END;

END

[DATABASE/SQLSERVER] 전화번호 ‘99999999999’ 문자열 → ‘999-9999-9999’ 문자열 변환하기

■ 전화번호 '99999999999' 문자열을 '999-9999-9999' 문자열로 변환하는 방법을 보여준다. ▶ 예제 코드 (SQL)


CREATE FUNCTION dbo.ConvertTelephoneNumberString
(
    @Source NVARCHAR(11)
)
RETURNS NVARCHAR(13)
BEGIN
    DECLARE @SourceLength INT;

    SET @SourceLength = LEN(@Source);

    RETURN
        CASE
            WHEN @SourceLength < 4 THEN LTRIM(RTRIM(SUBSTRING(@Source, 1, 3)))
            WHEN @SourceLength < 8 THEN LTRIM(RTRIM(SUBSTRING(@Source, 1, 3))) + '-' +
                                        LTRIM(RTRIM(SUBSTRING(@Source, 4, 4)))
            ELSE                        LTRIM(RTRIM(SUBSTRING(@Source, 1, 3))) + '-' +
                                        LTRIM(RTRIM(SUBSTRING(@Source, 4, 4))) + '-' +
                                        SUBSTRING(@Source, 8, 4)
        END;
END

CREATE FUNCTION dbo.ConvertTelephoneNumberString

(

@Source NVARCHAR(11)

)

RETURNS NVARCHAR(13)

BEGIN

DECLARE @SourceLength INT;

SET @SourceLength = LEN(@Source);

RETURN

CASE

WHEN @SourceLength < 4 THEN LTRIM(RTRIM(SUBSTRING(@Source, 1, 3)))

WHEN @SourceLength < 8 THEN LTRIM(RTRIM(SUBSTRING(@Source, 1, 3))) + '-' +

LTRIM(RTRIM(SUBSTRING(@Source, 4, 4)))

ELSE LTRIM(RTRIM(SUBSTRING(@Source, 1, 3))) + '-' +

LTRIM(RTRIM(SUBSTRING(@Source, 4, 4))) + '-' +

SUBSTRING(@Source, 8, 4)

END;

END