가라

CREATE TABLE TB_DATE (
  [Date] datetime
, [Year] smallint
, [Quarter] tinyint
, [Month] tinyint
, [Day] smallint -- from 1 to 366 = 1st to 366th day in a year
, [Week] tinyint -- from 1 to 54 = the 1st to 54th week in a year;
, [Monthly_week] tinyint -- 1/2/3/4/5=1st/2nd/3rd/4th/5th week in a month
, [Week_day] tinyint -- 1=Mon, 2=Tue, 3=Wed, 4=Thu, 5=Fri, 6=Sat, 7=Sun
)

WITH   C0   AS (SELECT c FROM (VALUES(1),(1)) AS D(c)),
  C1   AS (SELECT 1 AS c FROM C0 AS A CROSS JOIN C0 AS B),
  C2   AS (SELECT 1 AS c FROM C1 AS A CROSS JOIN C1 AS B),
  C3   AS (SELECT 1 AS c FROM C2 AS A CROSS JOIN C2 AS B),
  C4   AS (SELECT 1 AS c FROM C3 AS A CROSS JOIN C3 AS B),
  C5   AS (SELECT 1 AS c FROM C4 AS A CROSS JOIN C3 AS B),
  C6   AS (select rn=row_number() over (order by c)  from C5),
  C7   as (select [date]=dateadd(day, rn-1, '19000101') FROM C6 WHERE rn <= datediff(day, '19000101', '99991231')+1)

INSERT INTO TB_DATE ([year], [quarter], [month], [week], [day], [monthly_week], [week_day], [date])
SELECT datepart(yy, [DATE]), datepart(qq, [date]), datepart(mm, [date]), datepart(wk, [date])
     , datediff(day, dateadd(year, datediff(year, 0, [date]), 0), [date])+1
 , datepart(week, [date]) -datepart(week, dateadd(month, datediff(month, 0, [date]) , 0))+1
 , CASE WHEN datepart(dw, [date])+@@datefirst-1 > 7 THEN (datepart(dw, [date])+@@datefirst-1)%7 ELSE datepart(dw, [date])+@@datefirst-1 END
     , [date]
FROM C7

WITH tmp AS ( 
SELECT idx=1, dt = cast('20090101' AS datetime) UNION ALL SELECT idx = idx + 1, dt = dateadd(d, 1, dt) 
FROM tmp WHERE dt < cast('20101231' AS datetime) ) 
SELECT dt , year = datepart(year, dt) , month = datepart(month, dt) , 
day = datepart(day, dt) , weekofyear = datepart(wk , dt) , weekofmonth = datepart(wk, dt) - datepart(wk, left(convert(varchar, dt, 112), 6)+ '01') + 1 , 
요일 = datename(w, dt) , 분기 = datepart(q, dt) , 반기 = case when datepart(month,dt) BETWEEN 1 AND 6 then '상반기' else '하반기' end 
FROM tmp OPTION (maxrecursion 0)

master.dbo.xp_fixeddrives

WITH TA AS ( 
SELECT CAST('20000101' AS DATETIME)  [Date] 
UNION ALL
SELECT DATEADD(D, 1, [Date])
FROM TA WHERE [Date] < CAST('21001231' AS DATETIME)
) 
INSERT INTO [테이블이름] ([Date],[Year],[Quarter],[Month],[Day])
SELECT [Date], DATEPART(YEAR, [Date]) [Year], DATEPART(QUARTER, [Date]) [Quarter], DATEPART(MONTH, [Date]) [Month], DATEPART(DAY, [Date]) [Day]
FROM TA
OPTION (MAXRECURSION 0)

Q : MSSQL 사용중인데 DB에 설치된 트리거를 확인하고 싶어요.

A : 아래 질의문으로 간단하게 확인가능합니다.

SELECT object_name(parent_id) [target], name
FROM sys.triggers
ORDER BY object_name(parent_id)

Q : 테이블 'table_name' 에 있는 ID 열의 명시적 값은 열 목록이 사용되고 IDENTITY_INSERT가 ON일 때만 지정할 수 있습니다. 메시지가 나오면 INSERT가 되지 않아요.

A : 문구처럼 열 목록이 사용되고 IDENTITY_INSERT 값이 ON 으로 입력해주면 되요. 작업뒤에는 다시 OFF 해 주고요

예)

SET IDENTITY_INSERT MyTable1 ON
INSERT INTO MyTable1 (MyCol1, MyCol2, MyCol3)

SELECT MyColA, MyColB, MyColC FROM MyTableA

SET IDENTITY_INSERT MyTable1 OFF

* EXISTS (EXISTS, NOT EXISTS 사용가능)

IF EXISTS (SELECT TargetID FROM TABLE_SAMPLE WITH(NOLOCK) WHERE SourceID = '12345')
    BEGIN
        -- 위에 SELECT 문의 결과가 존재하면 실행됨
        SELECT 'TRUE'
    END
ELSE
    BEGIN
        -- 위에 SELECT 문의 결과가 없으면 실행됨
        SELECT 'FALSE'
    END

* IF
DECLARE @TargetID INT
SET @TargetID = 1111
IF TargetID = 2222
    BEGIN
        -- 위에 TargetID = 2222 문의 결과가 True 일때 실행
        SELECT 'TRUE'
    END
ELSE
    BEGIN
        -- 위에 TargetID = 2222 문의 결과가 False 일때 실행
        SELECT 'FALSE'
    END

이론)

? 바로 왼쪽에 있는 문자가 일치하는 문자

실습)

-- 컬럼내 영문자/숫자 아닌 문자 검색

SELECT * FROM 테이블이름 WHERE 컬럼이름 LIKE '%[^0-9A-Za-z]%'

-- 문장내에서 특정 문구를 이동/복사

* 인덱스 생성

CREATE INDEX IndexName ON TableName(ColumnName1, ColumnName2)

예:컬럼이 2개일때) CREATE INDEX IDX_USER ON tblData(UserName, UserSize)

* 인덱스 제거

DROP INDEX TableName.IndexName

예) DROP INDEX tblData.IDX_USER

* 질의문

DECLARE @nIndex INT

SET @nIndex = 1

WHILE @nIndex <= 10

BEGIN  

PRINT @nIndex

SET @nIndex = @nIndex + 1

END 

* 결과

1

2

3

4

5

6

7

8

9

10

탭

UPDATE 테이블이름 SET 컬럼이름 = REPLACE(컬럼이름, CHAR(9), '')

줄바꿈

UPDATE 테이블이름 SET 컬럼이름 = REPLACE(컬럼이름, CHAR(10), '')

캐리지 리턴

SELECT ROW_NUMBER() OVER(ORDER BY Value), * FROM (
SELECT '1' Value UNION ALL
SELECT '2' Value UNION ALL
SELECT '3' Value UNION ALL
SELECT '3' Value UNION ALL
SELECT '5' Value UNION ALL
SELECT '6' Value) SampleTable
    
SELECT RANK() OVER(ORDER BY Value), * FROM (
SELECT '1' Value UNION ALL
SELECT '2' Value UNION ALL
SELECT '3' Value UNION ALL
SELECT '3' Value UNION ALL
SELECT '5' Value UNION ALL
SELECT '6' Value) SampleTable
 
SELECT DENSE_RANK() OVER(ORDER BY Value), * FROM (
SELECT '1' Value UNION ALL
SELECT '2' Value UNION ALL
SELECT '3' Value UNION ALL
SELECT '3' Value UNION ALL
SELECT '5' Value UNION ALL
SELECT '6' Value) SampleTable

SELECT ROW_NUMBER() OVER(PARTITION BY [Group] ORDER BY Value), * FROM (
SELECT '1' Value, 'A' [Group] UNION ALL
SELECT '2' Value, 'A' [Group] UNION ALL
SELECT '3' Value, 'A' [Group] UNION ALL
SELECT '3' Value, 'B' [Group] UNION ALL
SELECT '5' Value, 'B' [Group] UNION ALL
SELECT '6' Value, 'B' [Group] ) SampleTable

1. 테이블 수정하기
SP_RENAME 'OLD_TABLE_NAME', 'NEW_TABLE_NAME'

2. 컬럼 수정하기
SP_RENAME 'TABLE_NAME'.'OLD_COLUMN_NAME', 'NEW_COLUMN_NAM'
또는
ALTER TABLE '테이블명' ALTER COLUMN '컬럼명' VARCHAR(16) NOT NULL

3. 컬럼 삭제하기
ALTER TABLE '테이블명' DROP '컬럼명'

4. 컬럼 추가하기
ALTER TABLE '테이블명' ADD '컬럼명' VARCHAR(16) NOT NULL

[SEED값 초기화]

DBCC CHECKIDENT('테이블명', RESEED, 0)

[SEED값 확인]

SELECT @@IDENTITY

SELECT SCOPE_IDENTITY()

SELECT IDENT_CURRENT('테이블명');

-- 테이블명 검색

SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_NAME = '테이블명'

-- 컬럼명 검색

SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME = '컬럼명'

-- PK 검색

SELECT * FROM information_schema.KEY_COLUMN_USAGE

-- 전체 테이블명 및 컬럼명 검색

SELECT B.name + '.' + A.name [TableName], D.value [TableDesc], C.name [ColumnName], E.value [ColumnDesc]    

FROM SYSOBJECTS A

INNER JOIN SYSUSERS B ON A.uid = B.uid

INNER JOIN SYSCOLUMNS C ON A.id = C.id

LEFT OUTER JOIN SYS.EXTENDED_PROPERTIES D ON A.id = D.major_id AND D.minor_id = 0 AND D.name = 'MS_Description'

LEFT OUTER JOIN SYS.EXTENDED_PROPERTIES E ON C.id = E.major_id AND C.colid = E.minor_id AND E.name = 'MS_Description'

WHERE A.type = 'U'

ORDER BY A.name, C.colorder

/*

SELECT * FROM SYSOBJECTS

SELECT * FROM SYSUSERS

SELECT * FROM SYSCOLUMNS

SELECT * FROM SYS.EXTENDED_PROPERTIES

*/

Q : MS-SQL 특정단어가 포함된 프로시저 찾기 (Stored Procedure 내용검색)

SELECT A.NAME FROM SYSOBJECTS A
INNER JOIN SYSCOMMENTS B ON A.ID = B.ID
WHERE A.Type = 'P' AND B.Text LIKE '%찾고자하는 문구%'
ORDER BY NAME

SELECT * FROM SampleTable
ORDER BY
CASE
WHEN SampleNo IN (5, 8)      THEN 1
WHEN SampleNo IN (2, 3)      THEN 2
WHEN SampleNo IN (4, 6)      THEN 3
WHEN SampleNo IN (7, 9, 10) THEN 4
WHEN SampleNo IN (11)        THEN 5
WHEN SampleNo IN (12)        THEN 6
ELSE 7 END ASC

DECLARE @TUserID VARCHAR(16)
DECLARE @TUserList VARCHAR(8000)
SET @TUserList = '이순신,김유신,강감찬,을지문덕,광개토대왕'

WHILE CHARINDEX(',', @TUserList) <> 0 OR LEN(RTRIM(@TUserList)) > 0
BEGIN
     IF (CHARINDEX(',', @TUserList) = 0)
     BEGIN
          SET @TUserID = @TUserList
          SET @TUserList = ''
     END
     ELSE
     BEGIN
          SELECT @TUserID = SUBSTRING(@TUserList, 0, CHARINDEX(',', @TUserList))
          SET @TUserList  = SUBSTRING(@TUserList, CHARINDEX(',', @TUserList) + 1, LEN(@TUserList))
     END

     PRINT(@TUserID)
END

-- ◈ Len : 문자열 길이 반환
--    SELECT LEN('student@bit.co.kr') -- 결과 : 17

-- ◈ Charindex : 문자열에서 특정 문자열의 위치를 반환
--    SELECT CHARINDEX('@', 'student@bit.co.kr') -- 결과 : 8

-- ◈ Substring : 문자열 일부를 반환
--    SELECT SUBSTRING('student@bit.co.kr', 8, 4) -- 결과 : @bit

-- ◈ 예] Len, Charindex, Substring 이용하여 메일주소에서 도메인명만 추출
--        DECLARE @nLen INT
--        SET     @nLen = LEN('student@bit.co.kr')
--        DECLARE @nStart INT
--        SET     @nStart = CHARINDEX('@', 'student@bit.co.kr')
--        SELECT SUBSTRING('student@bit.co.kr', @nStart, @nLen + 1 - @nStart) -- 결과 : @bit.co.kr

SELECT CONVERT(VARCHAR, GETDATE(), 0)      -- 결과 : 09 22 2011 2:53PM
SELECT CONVERT(VARCHAR, GETDATE(), 100)   -- 결과 : 09 22 2011 2:53PM

SELECT CONVERT(VARCHAR, GETDATE(), 1)      -- 결과 : 09/22/11
SELECT CONVERT(VARCHAR, GETDATE(), 101)   -- 결과 : 09/22/2011

SELECT CONVERT(VARCHAR, GETDATE(), 2)      -- 결과 : 11.09.22
SELECT CONVERT(VARCHAR, GETDATE(), 102)   -- 결과 : 2011.09.22

SELECT CONVERT(VARCHAR, GETDATE(), 3)      -- 결과 : 22/09/11
SELECT CONVERT(VARCHAR, GETDATE(), 103)   -- 결과 : 22/09/2011

SELECT CONVERT(VARCHAR, GETDATE(), 4)      -- 결과 : 22.09.11
SELECT CONVERT(VARCHAR, GETDATE(), 104)   -- 결과 : 22.09.2011

SELECT CONVERT(VARCHAR, GETDATE(), 5)      -- 결과 : 22-09-11
SELECT CONVERT(VARCHAR, GETDATE(), 105)   -- 결과 : 22-09-2011

SELECT CONVERT(VARCHAR, GETDATE(), 6)      -- 결과 : 22 09 11
SELECT CONVERT(VARCHAR, GETDATE(), 106)   -- 결과 : 22 09 2011

SELECT CONVERT(VARCHAR, GETDATE(), 7)      -- 결과 : 09 22, 11
SELECT CONVERT(VARCHAR, GETDATE(), 107)   -- 결과 : 09 22, 2011

SELECT CONVERT(VARCHAR, GETDATE(), 8)      -- 결과 : 14:54:43
SELECT CONVERT(VARCHAR, GETDATE(), 108)   -- 결과 : 14:54:43

SELECT CONVERT(VARCHAR, GETDATE(), 9)      -- 결과 : 09 22 2011 2:55:44:510PM
SELECT CONVERT(VARCHAR, GETDATE(), 109)   -- 결과 : 09 22 2011 2:55:44:510PM

SELECT CONVERT(VARCHAR, GETDATE(), 10)    -- 결과 : 09-22-11
SELECT CONVERT(VARCHAR, GETDATE(), 110)   -- 결과 : 09-22-2011

SELECT CONVERT(VARCHAR, GETDATE(), 11)    -- 결과 : 11/09/22
SELECT CONVERT(VARCHAR, GETDATE(), 111)   -- 결과 : 2011/09/22

SELECT CONVERT(VARCHAR, GETDATE(), 12)    -- 결과 : 110922
SELECT CONVERT(VARCHAR, GETDATE(), 112)   -- 결과 : 20110922

SELECT CONVERT(VARCHAR, GETDATE(), 13)    -- 결과 : 22 09 2011 14:57:26:873
SELECT CONVERT(VARCHAR, GETDATE(), 113)   -- 결과 : 22 09 2011 14:57:26:873

SELECT CONVERT(VARCHAR, GETDATE(), 14)    -- 결과 : 14:57:42:327
SELECT CONVERT(VARCHAR, GETDATE(), 114)   -- 결과 : 14:57:42:327

SELECT CONVERT(VARCHAR, GETDATE(), 20)    -- 결과 : 2011-09-22 14:58:14
SELECT CONVERT(VARCHAR, GETDATE(), 120)   -- 결과 : 2011-09-22 14:58:14

SELECT CONVERT(VARCHAR, GETDATE(), 21)    -- 결과 : 2011-09-22 14:58:32.473
SELECT CONVERT(VARCHAR, GETDATE(), 121)   -- 결과 : 2011-09-22 14:58:32.473

SELECT CONVERT(VARCHAR, GETDATE(), 126)   -- 결과 : 2011-09-22T14:59:43.240

SELECT CONVERT(VARCHAR, GETDATE(), 127)   -- 결과 : 2011-09-22T14:59:43.240