'DBTool/MSSQL' 카테고리의 글 목록 (2 Page)

--구글링을 찾아보니
--http://bytes.com/topic/sql-server/answers/84113-how-change-trimtrailingblanks-no-yes
Update syscolumns
set typestat = typestat |2
where id in ( object_id('테이블명') )
을 넣을려니..
아.. 이룬. 권한이 머시고 - -

음. 없다면,
한개씩 테이블 컬럼 수정(원본 그대로)을 하면 .. 짜잔 바뀜.
SET ANSI_PADDING ON

ALTER TABLE [dbo].[MR_InsufRecInfo]
ALTER COLUMN [ChosNo] [char](14) NULL

Posted by 말없제이

DTC 설정

DBTool/MSSQL 2013. 5. 20. 11:40

DTC 설정

참조 : http://cafe.naver.com/jjang12pro/578

일단 위 URL로 가셔서 읽어보세욤.

Posted by 말없제이

SQL 자리수대로 다른 문자로 표현

DBTool/MSSQL 2013. 5. 2. 19:26

SELECT STUFF('1234561234567',7,0,'-')
select REPLICATE('*',LEN('123'))

Posted by 말없제이

테이블 트리거확인

DBTool/MSSQL 2013. 2. 25. 09:49

-- 테이블 트리확인
select 'sp_helptext ' + t.name
FROM sys.sysobjects AS t
INNER JOIN sys.sysobjects AS o
ON t.parent_obj = o.id
WHERE
t.xtype ='TR'
and o.name like 'DSCTB_'

Posted by 말없제이

MSSQL - Comment대용 MS_Description ^^ List 추가함.

DBTool/MSSQL 2013. 2. 13. 15:21

참조 사이트 : http://msdn.microsoft.com/ko-kr/library/ms180047.aspx

'user','dbo' 요부분은 환경에 맞도록 수정해야 함~~~..

::fn_listextendedproperty 함수 사용시 컬럼명도 같이 넣어야 해서.. 이리저리 찾아서.

@D_CodeType = 'List' 도 만들어 놨어욤^^.

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

DECLARE @D_CodeType VARCHAR(12);
DECLARE @D_TableName VARCHAR(100);
DECLARE @D_ColumnName VARCHAR(100);
DECLARE @D_Value VARCHAR(500);
DECLARE @D_ColumnRefValue1 VARCHAR(100);
DECLARE @D_ColumnREFValue2 VARCHAR(100);

SET @D_CodeType = 'D';--S(C/U)/R/D/List
SET @D_TableName = 'DSCTB_LawInfo';
SET @D_ColumnName = 'LawDate';
SET @D_Value = '소송시작일';

SET @D_ColumnRefValue1 = null;
SET @D_ColumnREFValue2 = null;

IF(ISNULL(@D_ColumnName,'') <> '')
BEGIN
SET @D_ColumnRefValue1 = 'column';
SET @D_ColumnREFValue2 = @D_ColumnName;
END

IF(@D_CodeType = 'S')
BEGIN
  BEGIN TRY -- 생성개념으로, 이미 없는데 업데이트하면 에러발생~~
   exec sp_addextendedproperty 'MS_Description',@D_Value,'user','dbo', 'table',@D_TableName, @D_ColumnRefValue1, @D_ColumnREFValue2
  END TRY
  BEGIN CATCH
   exec sp_updateextendedproperty 'MS_Description',@D_Value,'user','dbo', 'table',@D_TableName, @D_ColumnRefValue1, @D_ColumnREFValue2
  END CATCH
END
ELSE IF(@D_CodeType = 'D')
BEGIN
  BEGIN TRY -- 삭제 없어도 에러발생^^..
   exec sp_dropextendedproperty 'MS_Description','user','dbo', 'table',@D_TableName, @D_ColumnRefValue1, @D_ColumnREFValue2
  END TRY
  BEGIN CATCH
   SELECT '삭제항목 없음'
  END CATCH
END
ELSE IF(@D_CodeType = 'R')
BEGIN -- 기본적인 조회인데.. 값을 다 넣어야 함.. 귀차니즘.
  SELECT *
  FROM ::fn_listextendedproperty(NULL, 'user', 'dbo', 'table',@D_TableName, @D_ColumnRefValue1, @D_ColumnREFValue2)
END
ELSE IF(@D_CodeType = 'List')
BEGIN -- 자잔.. 테이블 명만 넣으면..해당컬럼몽땅나옴. sp_help 명령어처럼(Alt+F1) 화면같이 해돌라고 하지말고 필요하신분이 수정하삼^^.

-- 아 왠지 만들어서 Cltr+F1 예약하면 편한듯한 - -

  SELECT P.name, p.value, i.name , c.name
FROM sys.extended_properties AS p
  INNER JOIN sys.objects AS i
  ON p.major_id = i.object_id
  LEFT OUTER join sys.syscolumns AS c
  ON p.major_id = c.id
   AND p.minor_id = c.colorder
  WHERE i.name = @D_TableName
  ORDER BY C.colorder ASC
END

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

헉,.. tistory로 올라가면 탭이 공백으로 바뀌네욤^^.

Posted by 말없제이

SQL 부분합 ROLLUP.

DBTool/MSSQL 2013. 2. 8. 12:00

SELECT
CASE
  WHEN ISNULL(S.FieldCode, '')  = '' THEN 'ZZ'
  WHEN ISNULL(S.FieldCode, '')  = '!' THEN 'AA'
             ELSE S.FieldCode
END            AS FieldCode,
CASE
  WHEN ISNULL(S.FieldCode, '')  = '' THEN '전체'
  WHEN ISNULL(S.FieldCode, '')  = '!' THEN '미정'
             ELSE MAX(dbo.DSCUF_EE_GetCodeName('Field', S.FieldCode, Default, Default))
END            AS FieldName,
CASE
  WHEN ISNULL(S.FieldCode, '')  = '' THEN '총계'
  WHEN ISNULL(S.VendorCode, '') = '' THEN '계'
             ELSE S.VendorCode
END            AS VendorCode,
CASE
  WHEN ISNULL(S.FieldCode, '')  = '' THEN '총계'
  WHEN ISNULL(S.VendorCode, '') = '' THEN '계'
             ELSE MAX(S.VendorName)
END            AS VendorName,
SUM(S.ContractCnt )       AS ContractCnt,
AVG(S.AvgPoint )        AS AvgPoint,
CASE
  WHEN S.VendorCode IS NULL    THEN '9999'
             ELSE CONVERT(VARCHAR , MAX(Ranking))
END            AS Ranking,
MAX(S.Remark)         AS Remark
FROM
(
SELECT
   ISNULL(C.FieldCode, '!')          AS FieldCode,
   MAX(dbo.DSCUF_EE_GetCodeName('Field', C.FieldCode, '', ''))  AS FieldName,
   B.VendorCode             AS VendorCode,
   MAX(B.CtrtVendorNm)            AS VendorName,
   COUNT(1)              AS ContractCnt,
   AVG(A.ApprTotalPoint)           AS AvgPoint,
   RANK() OVER (
        PARTITION BY C.FieldCode
         ORDER BY AVG(A.ApprTotalPoint) DESC
       )            AS Ranking,
   ''                AS Remark
   FROM
   dbo.DSCTB_EEApprTargetContract     AS A
   INNER JOIN dbo.DSCVW_EEContractInfo   AS B
     ON
      B.ContractNum = A.ContractNum
       AND B.OrdNum  = A.OrdNum
       AND 0 =
        (
        SELECT COUNT(1)
          FROM dbo.DSCTB_EEApprExceptContract
         WHERE
            ContractNum = B.ContractNum
             AND OrdNum  = B.OrdNum
         )
   LEFT JOIN dbo.DSCTB_EEContTypeFieldInfo  AS C
     ON
      B.WorkerClsCode   = C.ContTypeCode
   WHERE
    A.ApprFinishYN  = 'Y'
     AND A.ApprReplyYN  = 'N'
   GROUP
   BY C.FieldCode, B.VendorCode
  ) AS S
GROUP
BY S.FieldCode, S.VendorCode WITH ROLLUP