SQLSERVER Archives - 7 중 6 번째 페이지

[DATABASE/SQLSERVER] 데이터베이스 제약 조건 조회하기

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


SELECT
    A.TABLE_CATALOG      AS [Database]
   ,A.TABLE_SCHEMA       AS [Schema]
   ,A.TABLE_NAME         AS [Table]
   ,A.CONSTRAINT_CATALOG AS ConstraintDatabase
   ,A.CONSTRAINT_SCHEMA  AS ConstraintSchema
   ,A.CONSTRAINT_NAME    AS [Constraint]
   ,CASE A.CONSTRAINT_TYPE
        WHEN 'PRIMARY KEY' THEN 'PK'
        WHEN 'FOREIGN KEY' THEN 'FK'
        WHEN 'CHECK'       THEN 'CK'
        WHEN 'UNIQUE'      THEN 'UK'
        ELSE                    ''
    END                  AS ConstraintType
   ,CASE A.IS_DEFERRABLE
        WHEN 'YES' THEN 'Y'
        WHEN 'NO'  THEN 'N'
        ELSE            ''
    END                  AS IsDeferrable
   ,CASE A.INITIALLY_DEFERRED
        WHEN 'YES' THEN 'Y'
        WHEN 'NO'  THEN 'N'
        ELSE            ''
    END                 AS InitiallyDeferred
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS A
WHERE A.TABLE_NAME <> 'sysdiagrams'
ORDER BY
    A.TABLE_NAME      ASC
   ,A.CONSTRAINT_TYPE DESC;

SELECT

A.TABLE_CATALOG AS [Database]

,A.TABLE_SCHEMA AS [Schema]

,A.TABLE_NAME AS [Table]

,A.CONSTRAINT_CATALOG AS ConstraintDatabase

,A.CONSTRAINT_SCHEMA AS ConstraintSchema

,A.CONSTRAINT_NAME AS [Constraint]

,CASE A.CONSTRAINT_TYPE

WHEN 'PRIMARY KEY' THEN 'PK'

WHEN 'FOREIGN KEY' THEN 'FK'

WHEN 'CHECK' THEN 'CK'

WHEN 'UNIQUE' THEN 'UK'

ELSE ''

END AS ConstraintType

,CASE A.IS_DEFERRABLE

WHEN 'YES' THEN 'Y'

WHEN 'NO' THEN 'N'

ELSE ''

END AS IsDeferrable

,CASE A.INITIALLY_DEFERRED

WHEN 'YES' THEN 'Y'

WHEN 'NO' THEN 'N'

ELSE ''

END AS InitiallyDeferred

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS A

WHERE A.TABLE_NAME <> 'sysdiagrams'

ORDER BY

A.TABLE_NAME ASC

,A.CONSTRAINT_TYPE DESC;

[DATABASE/SQLSERVER] 데이터베이스 뷰 컬럼 조회하기

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


SELECT
    A.TABLE_CATALOG                       AS [Database]
   ,A.TABLE_SCHEMA                        AS [Schema]
   ,A.TABLE_NAME                          AS [Table]
   ,CONVERT(INT, A.ORDINAL_POSITION)      AS Sequence
   ,A.COLUMN_NAME                         AS [Column]
   ,UPPER(A.DATA_TYPE)                    AS DataType
   ,ISNULL(A.CHARACTER_MAXIMUM_LENGTH, 0) AS StringSize
   ,ISNULL(A.CHARACTER_OCTET_LENGTH, 0)   AS ByteSize
   ,ISNULL(A.NUMERIC_PRECISION, 0)        AS NumericPrecision
   ,ISNULL(A.NUMERIC_PRECISION_RADIX, 0)  AS NumericPrecisionRadix
   ,ISNULL(A.NUMERIC_SCALE, 0)            AS NumericScale
   ,ISNULL(A.DATETIME_PRECISION, 0)       AS DateTimePrecision
   ,CASE A.IS_NULLABLE
        WHEN 'YES' THEN 'NULL'
        ELSE            ''
    END                                   AS [NULL]
   ,(
        SELECT
            CASE
                WHEN COUNT(*) > 0 THEN 'Y'
                ELSE                   'N'
            END
        FROM  syscolumns AS B
        JOIN  sysobjects AS C ON C.id = B.id
        WHERE B.status = 128
        AND   C.xtype  = 'U'
        AND   C.name   = A.TABLE_NAME
        AND   B.name   = A.COLUMN_NAME
    )                                     AS IsIdentity
   ,ISNULL(A.DOMAIN_NAME, '')             AS DomainName
   ,ISNULL(A.COLUMN_DEFAULT, '')          AS [Default]
   ,ISNULL(A.CHARACTER_SET_CATALOG, '')   AS CharacterSetCatalog
   ,ISNULL(A.CHARACTER_SET_SCHEMA, '')    AS CharacterSetSchema
   ,ISNULL(A.CHARACTER_SET_NAME, '')      AS CharacterSetName
   ,ISNULL(A.COLLATION_CATALOG, '')       AS CollationCatalog
   ,ISNULL(A.COLLATION_NAME, '')          AS [CollationName]
   ,ISNULL(A.DOMAIN_CATALOG, '')          AS DomainCatalog
   ,ISNULL(A.DOMAIN_SCHEMA, '')           AS DomainSchema
FROM  INFORMATION_SCHEMA.COLUMNS AS A
WHERE A.TABLE_NAME IN (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'VIEW')
ORDER BY
    A.TABLE_NAME       ASC
   ,A.ORDINAL_POSITION ASC;

SELECT

A.TABLE_CATALOG AS [Database]

,A.TABLE_SCHEMA AS [Schema]

,A.TABLE_NAME AS [Table]

,CONVERT(INT, A.ORDINAL_POSITION) AS Sequence

,A.COLUMN_NAME AS [Column]

,UPPER(A.DATA_TYPE) AS DataType

,ISNULL(A.CHARACTER_MAXIMUM_LENGTH, 0) AS StringSize

,ISNULL(A.CHARACTER_OCTET_LENGTH, 0) AS ByteSize

,ISNULL(A.NUMERIC_PRECISION, 0) AS NumericPrecision

,ISNULL(A.NUMERIC_PRECISION_RADIX, 0) AS NumericPrecisionRadix

,ISNULL(A.NUMERIC_SCALE, 0) AS NumericScale

,ISNULL(A.DATETIME_PRECISION, 0) AS DateTimePrecision

,CASE A.IS_NULLABLE

WHEN 'YES' THEN 'NULL'

ELSE ''

END AS [NULL]

SELECT

CASE

WHEN COUNT(*) > 0 THEN 'Y'

ELSE 'N'

END

FROM syscolumns AS B

JOIN sysobjects AS C ON C.id = B.id

WHERE B.status = 128

AND C.xtype = 'U'

AND C.name = A.TABLE_NAME

AND B.name = A.COLUMN_NAME

) AS IsIdentity

,ISNULL(A.DOMAIN_NAME, '') AS DomainName

,ISNULL(A.COLUMN_DEFAULT, '') AS [Default]

,ISNULL(A.CHARACTER_SET_CATALOG, '') AS CharacterSetCatalog

,ISNULL(A.CHARACTER_SET_SCHEMA, '') AS CharacterSetSchema

,ISNULL(A.CHARACTER_SET_NAME, '') AS CharacterSetName

,ISNULL(A.COLLATION_CATALOG, '') AS CollationCatalog

,ISNULL(A.COLLATION_NAME, '') AS [CollationName]

,ISNULL(A.DOMAIN_CATALOG, '') AS DomainCatalog

,ISNULL(A.DOMAIN_SCHEMA, '') AS DomainSchema

FROM INFORMATION_SCHEMA.COLUMNS AS A

WHERE A.TABLE_NAME IN (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'VIEW')

ORDER BY

A.TABLE_NAME ASC

,A.ORDINAL_POSITION ASC;

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

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


SELECT
    A.TABLE_CATALOG AS [Database]
   ,A.TABLE_SCHEMA  AS [Schema]
   ,A.TABLE_NAME    AS [Table]
FROM     INFORMATION_SCHEMA.TABLES AS A
WHERE    A.TABLE_TYPE = 'VIEW'
ORDER BY A.TABLE_NAME ASC;

SELECT

A.TABLE_CATALOG AS [Database]

,A.TABLE_SCHEMA AS [Schema]

,A.TABLE_NAME AS [Table]

FROM INFORMATION_SCHEMA.TABLES AS A

WHERE A.TABLE_TYPE = 'VIEW'

ORDER BY A.TABLE_NAME ASC;

[DATABASE/SQLSERVER] 데이타베이스 테이블 조회하기

■ 데이타베이스 테이블을 조회하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT
    A.TABLE_CATALOG AS [Database]
   ,A.TABLE_SCHEMA  AS [Schema]
   ,A.TABLE_NAME    AS [Table]
FROM     INFORMATION_SCHEMA.TABLES AS A
WHERE    A.TABLE_TYPE = 'BASE TABLE'
ORDER BY A.TABLE_NAME;

SELECT

A.TABLE_CATALOG AS [Database]

,A.TABLE_SCHEMA AS [Schema]

,A.TABLE_NAME AS [Table]

FROM INFORMATION_SCHEMA.TABLES AS A

WHERE A.TABLE_TYPE = 'BASE TABLE'

ORDER BY A.TABLE_NAME;

[DATABASE/SQLSERVER] 데이타베이스 테이블 컬럼 조회하기

■ 데이타베이스 테이블 컬럼을 조회하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT
    A.TABLE_CATALOG                       AS [Database]
   ,A.TABLE_SCHEMA                        AS [Schema]
   ,A.TABLE_NAME                          AS [Table]
   ,CONVERT(INT, A.ORDINAL_POSITION)      AS Sequence
   ,A.COLUMN_NAME                         AS [Column]
   ,UPPER(A.DATA_TYPE)                    AS DataType
   ,ISNULL(A.CHARACTER_MAXIMUM_LENGTH, 0) AS StringSize
   ,ISNULL(A.CHARACTER_OCTET_LENGTH, 0)   AS ByteSize
   ,ISNULL(A.NUMERIC_PRECISION, 0)        AS NumericPrecision
   ,ISNULL(A.NUMERIC_PRECISION_RADIX, 0)  AS NumericPrecisionRadix
   ,ISNULL(A.NUMERIC_SCALE, 0)            AS NumericScale
   ,ISNULL(A.DATETIME_PRECISION, 0)       AS DateTimePrecision
   ,CASE A.IS_NULLABLE
        WHEN 'YES' THEN 'NULL'
        ELSE            ''
    END                                   AS [NULL]
   ,(
        SELECT
            CASE
                WHEN COUNT(*) > 0 THEN 'Y'
                ELSE                   'N'
            END
        FROM  syscolumns AS B
        JOIN  sysobjects AS C ON C.id = B.id
        WHERE B.status = 128
        AND   C.xtype  = 'U'
        AND   C.name   = A.TABLE_NAME
        AND   B.name   = A.COLUMN_NAME
    )                                     AS IsIdentity
   ,ISNULL(A.COLUMN_DEFAULT, '')          AS [Default]
   ,ISNULL(A.CHARACTER_SET_CATALOG, '')   AS CharacterSetCatalog
   ,ISNULL(A.CHARACTER_SET_SCHEMA, '')    AS CharacterSetSchema
   ,ISNULL(A.CHARACTER_SET_NAME, '')      AS CharacterSetName
   ,ISNULL(A.COLLATION_CATALOG, '')       AS CollationCatalog
   ,ISNULL(A.COLLATION_NAME, '')          AS [CollationName]
   ,ISNULL(A.DOMAIN_CATALOG, '')          AS DomainCatalog
   ,ISNULL(A.DOMAIN_SCHEMA, '')           AS DomainSchema
   ,ISNULL(A.DOMAIN_NAME, '')             AS DomainName
FROM  INFORMATION_SCHEMA.COLUMNS AS A
WHERE A.TABLE_NAME IN (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE')
ORDER BY
    A.TABLE_NAME       ASC
   ,A.ORDINAL_POSITION ASC;

SELECT

A.TABLE_CATALOG AS [Database]

,A.TABLE_SCHEMA AS [Schema]

,A.TABLE_NAME AS [Table]

,CONVERT(INT, A.ORDINAL_POSITION) AS Sequence

,A.COLUMN_NAME AS [Column]

,UPPER(A.DATA_TYPE) AS DataType

,ISNULL(A.CHARACTER_MAXIMUM_LENGTH, 0) AS StringSize

,ISNULL(A.CHARACTER_OCTET_LENGTH, 0) AS ByteSize

,ISNULL(A.NUMERIC_PRECISION, 0) AS NumericPrecision

,ISNULL(A.NUMERIC_PRECISION_RADIX, 0) AS NumericPrecisionRadix

,ISNULL(A.NUMERIC_SCALE, 0) AS NumericScale

,ISNULL(A.DATETIME_PRECISION, 0) AS DateTimePrecision

,CASE A.IS_NULLABLE

WHEN 'YES' THEN 'NULL'

ELSE ''

END AS [NULL]

SELECT

CASE

WHEN COUNT(*) > 0 THEN 'Y'

ELSE 'N'

END

FROM syscolumns AS B

JOIN sysobjects AS C ON C.id = B.id

WHERE B.status = 128

AND C.xtype = 'U'

AND C.name = A.TABLE_NAME

AND B.name = A.COLUMN_NAME

) AS IsIdentity

,ISNULL(A.COLUMN_DEFAULT, '') AS [Default]

,ISNULL(A.CHARACTER_SET_CATALOG, '') AS CharacterSetCatalog

,ISNULL(A.CHARACTER_SET_SCHEMA, '') AS CharacterSetSchema

,ISNULL(A.CHARACTER_SET_NAME, '') AS CharacterSetName

,ISNULL(A.COLLATION_CATALOG, '') AS CollationCatalog

,ISNULL(A.COLLATION_NAME, '') AS [CollationName]

,ISNULL(A.DOMAIN_CATALOG, '') AS DomainCatalog

,ISNULL(A.DOMAIN_SCHEMA, '') AS DomainSchema

,ISNULL(A.DOMAIN_NAME, '') AS DomainName

FROM INFORMATION_SCHEMA.COLUMNS AS A

WHERE A.TABLE_NAME IN (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE')

ORDER BY

A.TABLE_NAME ASC

,A.ORDINAL_POSITION ASC;

[DATABASE/SQLSERVER] 주차(Week Number)별 데이터 집계하기

■ 주차(Week Number)별 데이터를 집계하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT
    A.WeekNumber AS WeekNumber
   ,A.Name       AS Name
   ,COUNT(*)     AS [Count]
FROM
(
    SELECT
        A.Name                        AS Name       -- 명칭
       ,DATEPART(ww, DateTimeWritten) AS WeekNumber -- 주차
    FROM  PhotoBook AS A
    WHERE A.MenuID       = 44
    AND   A.CategoryName = '자연'
) A
WHERE A.WeekNumber BETWEEN 1 AND 10 -- 1 ~ 10 주차 데이터
GROUP BY
    A.WeekNumber
   ,A.Name;

SELECT

A.WeekNumber AS WeekNumber

,A.Name AS Name

,COUNT(*) AS [Count]

FROM

(

SELECT

A.Name AS Name -- 명칭

,DATEPART(ww, DateTimeWritten) AS WeekNumber -- 주차

FROM PhotoBook AS A

WHERE A.MenuID = 44

AND A.CategoryName = '자연'

) A

WHERE A.WeekNumber BETWEEN 1 AND 10 -- 1 ~ 10 주차 데이터

GROUP BY

A.WeekNumber

,A.Name;

[DATABASE/SQLSERVER] BULK INSERT문 사용하기

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


BULK INSERT [Table Name] FROM '[File Name].csv'
WITH
(
    FIELDTERMINATOR = ','
   ,TABLOCK
   ,ROWS_PER_BATCH  = 100000
);

BULK INSERT [Table Name] FROM '[File Name].csv'

WITH

(

FIELDTERMINATOR = ','

,TABLOCK

,ROWS_PER_BATCH = 100000

);

[DATABASE/SQLSERVER] 에러 발생시키기

■ 에러를 발생시키는 방법을 보여준다. ▶ 예제 코드 (SQL)


RAISERROR('[%s] 데이터베이스에서 %d번 에러가 발생했습니다.', 19, 1, @@Servername, 1) WITH LOG;

/*
메시지 50000, 수준 19, 상태 1, 줄 1
[VOYAGER] 데이터베이스에서 1번 에러가 발생했습니다.
*/

RAISERROR('[%s] 데이터베이스에서 %d번 에러가 발생했습니다.', 19, 1, @@Servername, 1) WITH LOG;

메시지 50000, 수준 19, 상태 1, 줄 1

[VOYAGER] 데이터베이스에서 1번 에러가 발생했습니다.

[DATABASE/SQLSERVER] 원 단위 절상/절사하기

■ 원 단위로 절상/절사하는 방법을 보여준다. ▶ 원 단위 절상하기 (SQL)


SELECT ROUND(324 + 4.0, -1); -- 결과값 : 330

SELECT ROUND(324 + 4.0, -1); -- 결과값 : 330

▶ 원 단위 절사하기 (SQL)


SELECT FLOOR(324 / 10) * 10; -- 결과값 : 320

SELECT FLOOR(324 / 10) * 10; -- 결과값 : 320

[DATABASE/SQLSERVER] 데이터베이스명 구하기

■ 데이터베이스명을 구하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT @@Servername AS DatabaseName;

/*
DatabaseName
------------
VOYAGER
*/

SELECT @@Servername AS DatabaseName;

DatabaseName

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

VOYAGER

[DATABASE/SQLSERVER] 디스크 드라이브 용량 구하기

■ 디스크 드라이브 용량을 구하는 방법을 보여준다. ▶ 예제 코드 (SQL)


EXECUTE master..xp_fixeddrives;

/*
drive MB 사용 가능
----- ------------
C	25865
D	520632
E	877771
*/

EXECUTE master..xp_fixeddrives;

drive MB 사용 가능

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

C 25865

D 520632

E 877771

[DATABASE/SQLSERVER] LIKE 연산자 대신 INSTR 함수 사용하기

■ LIKE 연산자 대신 INSTR 함수를 사용하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT A.* FROM Employee AS A WITH(NOLOCK) WHERE A.Name LIKE '%James%';

대신

SELECT A.* FROM Employee AS A WITH(NOLOCK) WHERE INSTR(A.Name, 'James') > 0;

SELECT A.* FROM Employee AS A WITH(NOLOCK) WHERE A.Name LIKE '%James%';

대신

SELECT A.* FROM Employee AS A WITH(NOLOCK) WHERE INSTR(A.Name, 'James') > 0;

[DATABASE/SQLSERVER] SELECT 명령 잠금 해제 설정하기

■ SELECT 명령 잠금 해제를 설정하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET NOCOUNT ON;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

[DATABASE/SQLSERVER] 테이블명과 행 수 표시하기

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


SELECT
    A.name   AS [TableName]
   ,B.[rows] AS [RowCount]
FROM SYSOBJECTS A
JOIN SYSINDEXES B ON B.id = A.id
WHERE A.xtype ='u'
AND   B.indid < 2
ORDER BY A.name ASC;

SELECT

A.name AS [TableName]

,B.[rows] AS [RowCount]

FROM SYSOBJECTS A

JOIN SYSINDEXES B ON B.id = A.id

WHERE A.xtype ='u'

AND B.indid < 2

ORDER BY A.name ASC;

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

[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 값을 찾아