[MSSQL] 관리를 위한 쿼리들

Database 2010.09.24 14:53 Posted by 애플자라
* 테이블 별 row 개수 확인

SELECT
    o.name, i.rows
FROM
    sysindexes i INNER JOIN
    sysobjects o ON i.id = o.id
WHERE
    i.indid < 2 
    AND o.xtype = 'U'
ORDER BY o.name


* 테이블 별 사용량 확인

SELECT
    table_name = CONVERT(varchar(30), min(o.name))
    ,table_size = LTRIM(str(sum(reserved) * 8192 / 1024., 15, 0) + 'KB')
FROM
    sysindexes i INNER JOIN
    sysobjects o ON o.id = i.id
WHERE
    i.indid in (0, 1, 255)
    AND o.xtype = 'U'
GROUP BY i.id
ORDER BY table_name

참고 - http://blog.vicki.co.kr/1006
TAG mssql

[MSSQL] MSSQL2000 ->2005 마이그레이션 attach

Database 2010.07.05 19:38 Posted by 애플자라


-- DB MSSQL attach
sp_attach_db @dbname = 'AMAILCS',
@filename1 = 'D:\MSSQL\Data\AmailCS\AMAILCS_Data.MDF',
@filename2 = 'D:\MSSQL\Log\AmailCS\AMAILCS_Log.LDF'

sp_attach_db @dbname = 'AMAILCS_DEV',
@filename1 = 'D:\MSSQL\Data\AmailCS_Dev\AMAILCS_DEV_Data.MDF',
@filename2 = 'D:\MSSQL\Log\AmailCS_Dev\AMAILCS_DEV_Log.LDF'

sp_attach_db @dbname = 'AMAILCSNEW',
@filename1 = 'D:\MSSQL\Data\AmailCS_New\AMAILCSNEW_Data.MDF',
@filename2 = 'D:\MSSQL\Log\AmailCS_New\AMAILCSNEW_Log.LDF'

sp_attach_db @dbname = 'CS',
@filename1 = 'D:\MSSQL\Data\CS\CS_Data.MDF',
@filename2 = 'D:\MSSQL\Log\CS\CS_Log.LDF'

sp_attach_db @dbname = 'EMS51_BMT',
@filename1 = 'D:\MSSQL\Data\EMS51_BMT\EMS51_BMT_Data.MDF',
@filename2 = 'D:\MSSQL\Log\EMS51_BMT\EMS51_BMT_Log.LDF'

sp_attach_db @dbname = 'EMS_BMT',
@filename1 = 'D:\MSSQL\Data\EMS_BMT\EMS_BMT_Data.MDF',
@filename2 = 'D:\MSSQL\Log\EMS_BMT\EMS_BMT_Log.LDF'

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

-- 데이터베이스 권한
use ems51_bmt
exec sp_change_users_login 'Update_One', 'ems51_bmt', 'ems51_bmt'

use ems_bmt
exec sp_change_users_login 'Update_One', 'ems_bmt', 'ems_bmt'

'Database' 카테고리의 다른 글

[ORACLE]OUTER JOIN  (0) 2010.08.26
IBM DB2에서 select and update,delete 하기  (0) 2010.07.19
[MSSQL] MSSQL2000 ->2005 마이그레이션 attach  (0) 2010.07.05
[MSSQL]일별,주별,월별 통계  (0) 2010.06.29
MSSQL 데이터 백업  (0) 2010.06.07
Oracle DELETE_TRIGGER  (0) 2010.05.31

[MSSQL]일별,주별,월별 통계

Database 2010.06.29 23:14 Posted by 애플자라

-- 일단위
Select DATEPART(dd, order_dt), count(order_no) From 주문테이블
group by DATEPART(dd, order_dt)
order by DATEPART(dd, order_dt)

 

-- 주 단위
Select DATEPART(ww, order_dt), count(order_no) From 주문테이블
group by DATEPART(ww, order_dt)
order by DATEPART(ww, order_dt)

 

-- 월단위
Select DATEPART(mm, order_dt), count(order_no) From 주문테이블
group by DATEPART(mm, order_dt)
order by DATEPART(mm, order_dt)

 

-- 년단위
Select DATEPART(yy, order_dt), count(order_no) From 주문테이블
group by DATEPART(yy, order_dt)
order by DATEPART(yy, order_dt)


--분기별
SELECT A.memyear,A.AA,SUM(A.CNT) AS CNT
FROM (
Select DATEPART(yy, regdate) as memyear,DATEPART(mm, regdate) as memmonth,  count(regdate) as cnt
,(CASE DATEPART(mm, regdate) WHEN '1' THEN '1' WHEN '2' THEN '1' WHEN '3' THEN '1'
WHEN '4' THEN '2' WHEN '5' THEN '2' WHEN '6' THEN '2'
WHEN '7' THEN '3' WHEN '8' THEN '3' WHEN '9' THEN '3'
WHEN '10' THEN '4'
WHEN '11' THEN '4'
WHEN '12' THEN '4' ELSE '0' END) AS AA
From SMEM01MEM
group by DATEPART(mm, regdate),DATEPART(yy, regdate)
--order by DATEPART(ww, regdate)
) A
group by A.memyear,A.AA
order by A.memyear,A.AA

'Database' 카테고리의 다른 글

IBM DB2에서 select and update,delete 하기  (0) 2010.07.19
[MSSQL] MSSQL2000 ->2005 마이그레이션 attach  (0) 2010.07.05
[MSSQL]일별,주별,월별 통계  (0) 2010.06.29
MSSQL 데이터 백업  (0) 2010.06.07
Oracle DELETE_TRIGGER  (0) 2010.05.31
Oracle 짝수,홀수 구별  (0) 2010.05.26

MSSQL 데이터 백업

Database 2010.06.07 20:41 Posted by 애플자라

USE master
GO

--EXEC sp_addumpdevice 'disk', 'AMAILCS_MSSQL_Backup', 'D:\CS_Amail_Backup\AMAILCS_MSSQL_Backup.bak'
--EXEC sp_addumpdevice 'disk', 'AMAILCS_DEV_MSSQL_Backup', 'D:\CS_Amail_Backup\AMAILCS_DEV_MSSQL_Backup.bak'
--EXEC sp_addumpdevice 'disk', 'AMAILCSNEW_MSSQL_Backup', 'D:\CS_Amail_Backup\AMAILCSNEW_MSSQL_Backup.bak'
--EXEC sp_addumpdevice 'disk', 'CS_MSSQL_Backup', 'D:\CS_Amail_Backup\CS_MSSQL_Backup.bak'
--EXEC sp_addumpdevice 'disk', 'EMS_BMT_MSSQL_Backup', 'D:\CS_Amail_Backup\EMS_BMT_MSSQL_Backup.bak'
--EXEC sp_addumpdevice 'disk', 'EMS51_BMT_MSSQL_Backup', 'D:\CS_Amail_Backup\EMS51_BMT_MSSQL_Backup.bak'
--EXEC sp_addumpdevice 'disk', 'EMS52_TEST_MSSQL_Backup', 'D:\CS_Amail_Backup\EMS52_TEST_MSSQL_Backup.bak'
--EXEC sp_addumpdevice 'disk', 'EMS61_MSSQL_Backup', 'D:\CS_Amail_Backup\EMS61_MSSQL_Backup.bak'
--EXEC sp_addumpdevice 'disk', 'EMS61_1_MSSQL_Backup', 'D:\CS_Amail_Backup\EMS61_1_MSSQL_Backup.bak'
--EXEC sp_addumpdevice 'disk', 'MobileEms52_MSSQL_Backup', 'D:\CS_Amail_Backup\MobileEms52_MSSQL_Backup.bak'
--EXEC sp_addumpdevice 'disk', 'ROUGH_MSSQL_Backup', 'D:\CS_Amail_Backup\ROUGH_MSSQL_Backup.bak'

DBCC SHRINKDATABASE(AMAILCS)
DBCC SHRINKDATABASE(AMAILCS_DEV)
DBCC SHRINKDATABASE(AMAILCSNEW)
DBCC SHRINKDATABASE(CS)
DBCC SHRINKDATABASE(EMS_BMT)
DBCC SHRINKDATABASE(EMS51_BMT)
DBCC SHRINKDATABASE(EMS52_TEST)
DBCC SHRINKDATABASE(EMS61)
DBCC SHRINKDATABASE(EMS61_1)
DBCC SHRINKDATABASE(MobileEms52)
DBCC SHRINKDATABASE(ROUGH)

BACKUP DATABASE AMAILCS      TO AMAILCS_MSSQL_Backup WITH INIT;
BACKUP DATABASE AMAILCS_DEV   TO AMAILCS_DEV_MSSQL_Backup WITH INIT;
BACKUP DATABASE AMAILCSNEW   TO AMAILCSNEW_MSSQL_Backup WITH INIT;
BACKUP DATABASE CS           TO CS_MSSQL_Backup WITH INIT;
BACKUP DATABASE EMS_BMT      TO EMS_BMT_MSSQL_Backup WITH INIT;
BACKUP DATABASE EMS51_BMT     TO EMS51_BMT_MSSQL_Backup WITH INIT;
BACKUP DATABASE EMS52_TEST   TO EMS52_TEST_MSSQL_Backup WITH INIT;
BACKUP DATABASE EMS61        TO EMS61_MSSQL_Backup WITH INIT;
BACKUP DATABASE EMS61_1      TO EMS61_1_MSSQL_Backup WITH INIT;
BACKUP DATABASE MobileEms52   TO MobileEms52_MSSQL_Backup WITH INIT;
BACKUP DATABASE ROUGH        TO ROUGH_MSSQL_Backup WITH INIT;

'Database' 카테고리의 다른 글

[MSSQL] MSSQL2000 ->2005 마이그레이션 attach  (0) 2010.07.05
[MSSQL]일별,주별,월별 통계  (0) 2010.06.29
MSSQL 데이터 백업  (0) 2010.06.07
Oracle DELETE_TRIGGER  (0) 2010.05.31
Oracle 짝수,홀수 구별  (0) 2010.05.26
[ORACLE] 구분자_LIST형_맵핑형_조합형쿼리  (0) 2009.11.04

[MSSQL] DB Shrink 파일 지정 없이 축소하기

Database 2009.08.18 11:56 Posted by 애플자라

파일 지정 없이 축소하기

[따라하기] 파일 지정 없이 Sample 데이터베이스 전체 크기 중에서 10%의 여유공간이 남도록 파일 크기를 축소합니다.
DBCC SHRINKDATABASE (ems50, 10)
GO

[MSSQL] UPDATE~SELECT 문의 사용

Database 2009.05.13 10:30 Posted by 애플자라

MSSQL ==> B 테이블 SELECT 한것을 A테이블에 업데이트 치기@_@;; 죽을뻔함

UPDATE A SET
A.CUSTOMER_KEY = B.MEMBER_ID,
A.SEND_DT = CONVERT(CHAR(8),B.DELIVER_TIME,112),
A.SEND_TM = REPLACE(CONVERT(VARCHAR(20),B.DELIVER_TIME,108),':',''),
A.ERROR_CD = B.ERROR_CODE
 from nvecaresendlog A, smsauto_list_03 B
where B.workday ='20090512'
and B.seqno='16'
AND A.CUSTOMER_KEY = B.MEMBER_ID

참고 - http://blog.naver.com/rainbow8830/70030692861

-- 다른유형
UPDATE A
 SET A.JobTitle = B.UserJobTitle
FROM BackDatabase.dbo.DATTACHINFO A, Appro.dbo.ATTACHINFO B
where A.DocID=B.DocID
and A.FileSN=B.FileSN 

 

update customer aa
set (home_address)= (select home_address from customer_temp where customer_id=aa.customer_id);

[MSSQL] TRIGGER

Database 2009.03.27 13:10 Posted by 애플자라


이번 *****증권 업체를 맡아서 TRIGGER를 한번 해봤다.

- 고객테이블에 데이터 변경이 일어나면 무조건,
-- 바뀌기전 데이터와 바뀐후 데이터를, 기록테이블에 저장하는 트리거
CREATE TRIGGER SMSAUTO_LIST_03_INSERT_TRIGGER
ON SMSAUTO_LIST_03
AFTER INSERT
AS
-- SMSAUTO_LIST_03 테이블에 INSERT가 일어나면
-- 이를 NVECARESENDLOG 테이블에 같은 값을 삽입한다.
INSERT INTO NVECARESENDLOG(RESULT_SEQ, CUSTOMER_KEY, CUSTOMER_NM, CUSTOMER_EMAIL, ERROR_CD)
 SELECT RESULT_SEQ, MEMBER_ID AS CUSTOMER_KEY, TO_NAME AS CUSTOMER_NM, TO_PHONE AS CUSTOMER_EMAIL , ERROR_CODE AS ERROR_CD FROM inserted
GO

 

-- SMSAUTO_LIST_03 하는 테이블에 대해서
-- ERROR_CD에 Update가 발생한 경우에 실행되는 트리거
CREATE TRIGGER SMSAUTO_LIST_03_UPDATE_TRIGGER
ON SMSAUTO_LIST_03
AFTER UPDATE
AS
   DECLARE @ECARE_NO VARCHAR(15)
   DECLARE @RESULT_SEQ VARCHAR(16)
   DECLARE @CUSTOMER_KEY VARCHAR(50)
   DECLARE @SEND_DT VARCHAR(8)
   DECLARE @SEND_TM VARCHAR(6)
   DECLARE @ERROR_CD VARCHAR(4)
IF(UPDATE(ERROR_CODE))
BEGIN
  SELECT 
   @RESULT_SEQ = RESULT_SEQ,
   @CUSTOMER_KEY = MEMBER_ID,
   @SEND_DT = CONVERT(CHAR(8),SEND_TIME,112),
   @SEND_TM = REPLACE(CONVERT(VARCHAR(20),SEND_TIME,108),':',''),
   @ERROR_CD = ERROR_CODE
  FROM INSERTED
END
  UPDATE  NVECARESENDLOG
  SET     SEND_DT = @SEND_DT,
             SEND_TM = @SEND_TM,
             ERROR_CD = @ERROR_CD,
             TRIGGER_FLAG='Y',
             TRIGGER_DATE = GETDATE()
  WHERE  RESULT_SEQ = @RESULT_SEQ
  AND     CUSTOMER_KEY = @CUSTOMER_KEY

====================================================

-- SMSAUTO_LIST_03 하는 테이블에 대해서
-- SMSAUTO_LIST_03 테이블에 Update가 발생한 경우에 실행되는 트리거
CREATE TRIGGER SMSAUTO_LIST_03_UPDATE_TRIGGER
ON SMSAUTO_LIST_03
AFTER UPDATE
AS
   DECLARE @ECARE_NO VARCHAR(15)
   DECLARE @RESULT_SEQ VARCHAR(16)
   DECLARE @CUSTOMER_KEY VARCHAR(50)
   DECLARE @SEND_DT VARCHAR(8)
   DECLARE @SEND_TM VARCHAR(6)
   DECLARE @ERROR_CD VARCHAR(4)
IF(COLUMNS_UPDATED() > 0)
BEGIN
  SELECT
   @CUSTOMER_KEY = MEMBER_ID,
   @SEND_DT = CONVERT(CHAR(8),DELIVER_TIME,112),
   @SEND_TM = REPLACE(CONVERT(VARCHAR(20),DELIVER_TIME,108),':',''),
   @ERROR_CD = ERROR_CODE
  FROM INSERTED
END
  UPDATE  NVECARESENDLOG
  SET     SEND_DT = @SEND_DT,
          SEND_TM = @SEND_TM,
   ERROR_CD = @ERROR_CD,
   TRIGGER_FLAG='Y',
   TRIGGER_DATE = GETDATE()
  WHERE  CUSTOMER_KEY = @CUSTOMER_KEY

SELECT
   CASE WHEN LEN(CONVERT(VARCHAR(13),JUNO))=13 THEN SUBSTRING(JUNO,7,7)
   ELSE SUBSTRING(JUNO,4,7) END AS JUNO,
   JUNO NAMHA
FROM TEST_MEMBER

주민번호 or 사업자등록번호가 올때 LENGTH로 길이를 구분하여 짜르기
TAG length, mssql

[MSSQL] DATEADD사용하기

Database 2009.03.06 11:22 Posted by 애플자라


DATE관련 함수 몇개 더 추가해봅니다.


-- DATEADD를 이용한 날짜 변경
SELECT GETDATE() AS [TODAY], DATEADD(DAY, 3, GETDATE()) AS [ADD DATE]

TODAY                                                  ADD DATE                                              
------------------------------------------------------ ------------------------------------------------------
2008-10-20 09:58:25.450                                2008-10-23 09:58:25.450

-- DATEDIFF를 이용한 날짜 비교
SELECT GETDATE() AS [TODAY], DATEDIFF(DAY, '2007-12-25',GETDATE()) AS [DIFF]

TODAY                                                  DIFF       
------------------------------------------------------ -----------
2008-10-20 09:58:25.450                                300

-- DATEPART를 이용한 특정 날짜 부분 반환
SELECT
   DATEPART(YEAR, GETDATE()) AS [YEAR]
 , DATEPART(MONTH, GETDATE()) AS [MONTH]
 , DATEPART(DAY, GETDATE()) AS [DAY]

YEAR        MONTH       DAY        
----------- ----------- -----------
2008        10          20

 


>SQL 에서 datetime 형식의 시간에  원하는 만큼을 추가하려면 어찌해야할까...

dateadd(Type, Add, Date)  을 사용하면 됩니다.

    시간 추가.
    Date 시간에 원하는 시간을 더해서 구해 줍니다. 

    Type 에 입력될 항목

        year , yy, y
        month, m, mm
        day, d, dd
        week, w, wk
        hour, hh
        minute, m
        second, s, ss
        millisecond, ms

    Add 에 입력될 항목

        Type 에 따른 값. dateadd(year, 1  입력시  1년 추가.

    Date 추가할 기준 시간

        2008-10-10  or 2008-10-10 20:00:00  의  datetime or smalldatetime 형식


:샘플 

select dateadd(hour, 1, sdate) as vdate from XXX

XXX 테이블에서 sdate 라는 컬럼의 시간값을 얻어서  1시간을 더한 뒤에 vdate 라는 이름으로 얻는다.

출처 - 어딘지 까먹었음 @_@;;

TAG DATEADD, mssql

[ORACLE] 자동 증가 컬럼(Sequence) 사용하기

Database 2008.05.07 11:32 Posted by 애플자라

Unique Key를 생성하는 방법은 DBMS마다 차이가 있다.
MS-SQL은 IDENTITY를, MySQL은 auto_increment와 같이 쉽게 사용할 수 있는 방법이 있는데 오라클에서는 Sequence를 사용하여 다음과 같이 유사하게 구현할 수 있다


1. 자동증가컬럼을 사용하고자 하는 MYTABLE테이블을 생성한다.


       CREATE TABLE MYTABLE
               (ID NUMBER, NAME VARCHAR2(20));


2. CREATE SEQUENCE 라는 문장을 사용하여 SEQ_ID라는 이름의 시퀀스를 만든다.


       CREATE SEQUENCE SEQ_ID INCREMENT BY 1 START WITH 10000;

      -- INCREMENT BY 1 : 증가값은 1
      -- START WITH 10000 :  10000부터 증가


3.  테이블에 데이터 입력시에는 NEXTVAL이라는 슈도 칼럼(Pseudo-column)을 이용하여 시퀸스를 사용한다.


       INSERT INTO MYTABLE VALUES( SEQ_ID.NEXTVAL, '홍길동');

       -- CURRVAL : 현재 값을 반환 합니다. .
       -- NEXTVAL : 현재 시퀀스값의 다음 값을 반환 합니다.

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


* Sequence 구문


CREATE SEQUENCE sequence_name
       [START WITH n]
       [INCREMENT BY n]
       [MAXVALUE n | NOMAXVALUE]
       [MINVALUE n | NOMINVALUE]
       [CYCLE | NOCYCLE]


* START WITH
시퀀스의 시작 값을 지정합니다. n을 1로 지정하면 1부터 순차적으로 시퀀스번호가 증가 합니다.


* INCREMENT BY
시퀀스의 증가 값을 말합니다. n을 2로 하면 2씩 증가합니다.
START WITH를 1로 하고 INCREMENT BY를 2으로 하면 1, 3, 5,7,..
이렇게 시퀀스  번호가 증가하게 됩니다.


* MAXVALUE n | NOMAXVALUE
MAXVALUE는 시퀀스가 증가할수 있는 최대값을 말합니다.
NOMAXVALUE는 시퀀스의 값을 무한대로 지정합니다.


* MINVALUE n  | NOMINVALUE
MINVALUE는 시퀀스의 최소값을 지정 합니다.
기본값은 1이며, NOMINVALUE를 지정할 경우 최소값은 무한대가 됩니다


[사용규칙]


* NEXTVAL, CURRVAL을 사용할 수 있는 경우
   - subquery가 아닌 select문
   - insert문의 select절
   - insert문의 value절
   - update문의 set절


* NEXTVAL, CURRVAL을 사용할 수 없는 경우
   - view의 select절
   - distinct 키워드가 있는 select문
   - group by, having, order by절이 있는 select문
   - select, delete, update의 subquery
   - create table, alter table 명령의 default값


[수정과 삭제]


ALTER SEQUENCE sequence_name
       [INCREMENT BY n]
       [MAXVALUE n | NOMAXVALUE]
       [MINVALUE n | NOMINVALUE]
       [CYCLE | NOCYCLE]


START WITH는 수정할수 없습니다.
START WITH 절이 없다는 점을 빼고는 CREATE SEQUENCE와 같습니다.


DROP SEQUENCE sequence_name


자료참고 : http://www.oracleclub.com/