'(DB) MS SQL/SQL-Select' 카테고리의 글 목록

MS SQL.Select-Pivot. UnPivot (0)	2017.02.09
MS SQL.Select-java vo자동생성, procedure arg 자동생성 (0)	2017.02.09
MS SQL.Select-CRUD 문장 자동 만들어주기 (0)	2017.02.09
MS SQL.SELECT-레코드를 한 컬럼으로 만들기 (0)	2017.02.09

MS SQL.Select-java vo자동생성, procedure arg 자동생성

(DB) MS SQL/SQL-Select 2017. 2. 9. 17:06

[ MS SQL.Select-java vo자동생성, procedure arg 자동생성(컬럼기준) ]

--*******************************************************************
Declare @Table_name varchar(50);
set @Table_name = 'DeliveryPlan';  
--*******************************************************************

-------------------------------------------------------------------------
-- 1. VO만들기 위한 컬럼 목록
-------------------------------------------------------------------------
SELECT distinct
     '        private String ' 
   + dbo.GET_PADDING(A.COLUMN_NAME, 15) 
   + (CASE WHEN A.DATA_TYPE = 'int'     THEN '= "0";'
           WHEN A.DATA_TYPE = 'numeric' THEN '= "0";' 
           WHEN A.DATA_TYPE = 'number'  THEN '= "0";' 
           ELSE '= "";' END) AS VO_COLUMN_LIST
  FROM INFORMATION_SCHEMA.COLUMNS A LEFT OUTER JOIN SYS.EXTENDED_PROPERTIES B 
                                      ON     B.major_id = object_id(A.TABLE_NAME) 
                                         AND A.ORDINAL_POSITION = B.minor_id
                                    LEFT OUTER JOIN ( SELECT object_id(objname) AS TABLE_ID
                                                           , VALUE
                                                        FROM ::FN_LISTEXTENDEDPROPERTY(NULL, 'User','dbo','table', NULL, NULL, NULL)
                                                    ) C
                                      ON object_id(A.TABLE_NAME) = C.TABLE_ID
 WHERE 1=1
   AND A.TABLE_NAME IN  (@Table_name) 
 --WHERE A.TABLE_NAME IN ('SOT_HDR', 'SOT_SPEC', 'SOT_ITEM', 'SOT_OPTION', 'SOT_SET_TYPE')  
                      --'SO_HDR', 'SO_SPEC', 'SO_LOGIST', 'SO_ITEM', 'SO_OPTION', 'SO_STATUS'
 ;

-------------------------------------------------------------------------
-- 2. Procedure 입력 Argument 컬럼 목록
-------------------------------------------------------------------------
SELECT '     @ROW_STS        varchar(1) ' AS Procedure_PARAM UNION ALL
SELECT --DISTINCT A.TABLE_NAME, 
       '   , @' + dbo.GET_PADDING(A.COLUMN_NAME, 15) 
     + 
     + (CASE WHEN A.DATA_TYPE = 'int'      THEN 'int'
             WHEN A.DATA_TYPE = 'numeric'  THEN 'numeric(' + CAST(A.NUMERIC_PRECISION AS VARCHAR) + ',' + CAST(A.NUMERIC_SCALE AS VARCHAR) + ')'
             WHEN A.DATA_TYPE = 'datetime' THEN 'datetime' 
             WHEN A.DATA_TYPE = 'varchar'  THEN 'varchar(' + (CAST(A.CHARACTER_MAXIMUM_LENGTH AS VARCHAR)) + ')'
             ELSE 'error - ' + A.DATA_TYPE END)
  FROM INFORMATION_SCHEMA.COLUMNS A LEFT OUTER JOIN SYS.EXTENDED_PROPERTIES B 
                                      ON     B.major_id = object_id(A.TABLE_NAME) 
                                         AND A.ORDINAL_POSITION = B.minor_id
                                    LEFT OUTER JOIN ( SELECT object_id(objname) AS TABLE_ID
                                                           , VALUE
                                                        FROM ::FN_LISTEXTENDEDPROPERTY(NULL, 'User','dbo','table', NULL, NULL, NULL)
                                                    ) C
                                      ON object_id(A.TABLE_NAME) = C.TABLE_ID
 WHERE 1=1
    --AND A.COLUMN_NAME IN ('Dealer_ID','RqstDate','DeliveryDate','SO_NO','SaveUser','SO_Status','OrderType','Contract_ID','SaveDate')

AND A.TABLE_NAME IN (@Table_name)  
   --AND A.COLUMN_NAME IN ('FrameType','ItemSize','Pos','AccType')
 ;

-------------------------------------------------------------------------
-- 2. Table 컬럼 상세 정보
-------------------------------------------------------------------------
SELECT A.TABLE_NAME
     , C.VALUE                                              AS 테이블명
     , A.COLUMN_NAME                                        AS 컬럼명
     , A.DATA_TYPE                                          AS 타입
     , ISNULL(CAST(A.CHARACTER_MAXIMUM_LENGTH AS VARCHAR)
     , CAST(A.NUMERIC_PRECISION AS VARCHAR) + ',' + CAST(A.NUMERIC_SCALE AS VARCHAR))     AS 컬럼길이
     , A.COLUMN_DEFAULT                                     AS 디폴트
     , A.IS_NULLABLE                                        AS null여부
     , B.VALUE                                              AS 컬럼_주석
     , A.ORDINAL_POSITION
  FROM INFORMATION_SCHEMA.COLUMNS A LEFT OUTER JOIN SYS.EXTENDED_PROPERTIES B 
                                      ON     B.major_id = object_id(A.TABLE_NAME) 
                                         AND A.ORDINAL_POSITION = B.minor_id
                                    LEFT OUTER JOIN ( SELECT object_id(objname) AS TABLE_ID
                                                           , VALUE
                                                        FROM ::FN_LISTEXTENDEDPROPERTY(NULL, 'User','dbo','table', NULL, NULL, NULL)
                                                    ) C
                                      ON object_id(A.TABLE_NAME) = C.TABLE_ID
 WHERE 1=1 
   AND A.TABLE_NAME IN(@Table_name)--, 'SO_ITEM', 'SO_SPEC', 'SO_FILES', 'SO_LOGIST', 'SO_STATUS')
   --AND A.COLUMN_NAME = 'DistChnlCode'
 ORDER BY A.TABLE_NAME, A.ORDINAL_POSITION  ;

저작자표시

'(DB) MS SQL > SQL-Select' 카테고리의 다른 글

MSSQL-Connect By. 조직도 조회 (0)	2017.04.18
MS SQL.Select-Pivot. UnPivot (0)	2017.02.09
MS SQL.Select-CRUD 문장 자동 만들어주기 (0)	2017.02.09
MS SQL.SELECT-레코드를 한 컬럼으로 만들기 (0)	2017.02.09

Posted by 농부지기

,

MS SQL.Select-CRUD 문장 자동 만들어주기

(DB) MS SQL/SQL-Select 2017. 2. 9. 17:05

[ MS SQL.Select-CRUD 문장 자동 만들어주기 ]

--*******************************************************************
declare  @tbl_nm                      nvarchar(150);
set      @tbl_nm = 'DO_HDR';
--*******************************************************************
--1. INSERT
SELECT AA.*
  FROM (
                --SELECT 'IF (@ROW_STS = ''I'')'      AS COLUMN_NAME, 'A' AS GUBN, -4 AS SEQ UNION ALL
                --SELECT '  BEGIN'                    AS COLUMN_NAME, 'A' AS GUBN, -3 AS SEQ UNION ALL
                SELECT '    INSERT INTO ' + @tbl_nm AS COLUMN_NAME, 'A' AS GUBN, -2 AS SEQ UNION ALL
                SELECT '             ( '                AS COLUMN_NAME, 'A' AS GUBN, -1 AS SEQ UNION ALL
                SELECT (CASE WHEN A.ORDINAL_POSITION = 1 
                             THEN '               ' 
                             ELSE '             , '   
                        END) + A.COLUMN_NAME, 'A' AS GUBN, A.ORDINAL_POSITION AS SEQ
                  FROM INFORMATION_SCHEMA.COLUMNS A
                 WHERE A.TABLE_NAME IN(@tbl_nm)                                            UNION ALL
                SELECT '      )'                   AS COLUMN_NAME, 'A' AS GUBN, 999 AS SEQ
           ) AS AA
  WHERE 1=1
  UNION ALL
 SELECT BB.*
   FROM (
                SELECT '    VALUES'      AS COLUMN_NAME, 'B' AS GUBN, -4 AS SEQ UNION ALL
                SELECT '             ( '     AS COLUMN_NAME, 'B' AS GUBN, -3 AS SEQ UNION ALL
                SELECT (CASE WHEN A.ORDINAL_POSITION = 1 
                             THEN '               '
                             ELSE '             , '   
                        END) + (CASE WHEN A.COLUMN_NAME = 'SaveDate'   THEN 'dbo.GET_USDate()'
                                     WHEN A.COLUMN_NAME = 'UpdateDate' THEN 'dbo.GET_USDate()'
                                     ELSE '@' +A.COLUMN_NAME
                                END)   , 'B' AS GUBN, A.ORDINAL_POSITION AS SEQ
                  FROM INFORMATION_SCHEMA.COLUMNS A
                 WHERE A.TABLE_NAME IN(@tbl_nm)                                 UNION ALL
                SELECT '                   ) ;'    AS COLUMN_NAME, 'B' AS GUBN, 999 AS SEQ
           ) AS BB
ORDER BY GUBN, SEQ
;

--2. UPDATE
--SELECT 'ELSE IF (@ROW_STS = ''U'')'  , -4 AS SEQ UNION ALL
SELECT '    UPDATE ' + @tbl_nm       , -3 AS SEQ UNION ALL
SELECT '       SET '                 , -2 AS SEQ UNION ALL
SELECT (CASE WHEN A.ORDINAL_POSITION = 1 
                             THEN '           ' 
                             ELSE '         , ' 
                        END) + dbo.GET_PADDING(A.COLUMN_NAME, 15) + ' = ' 
                     + (CASE WHEN A.COLUMN_NAME = 'SaveDate'   THEN 'dbo.GET_USDate()'
                                     WHEN A.COLUMN_NAME = 'UpdateDate' THEN 'dbo.GET_USDate()'
                                     ELSE '@' +A.COLUMN_NAME
                                END)
                                     , A.ORDINAL_POSITION AS SEQ
  FROM INFORMATION_SCHEMA.COLUMNS A
 WHERE A.TABLE_NAME IN(@tbl_nm)        
   AND A.COLUMN_NAME NOT IN('SaveUser', 'SaveDate')            UNION ALL
SELECT '     WHERE '                , 999 AS SEQ 
ORDER BY 2
;

--3. DELETE
--SELECT 'ELSE IF (@ROW_STS = ''D'')'  , -4 AS SEQ UNION ALL
SELECT '    DELETE FROM ' + @tbl_nm      , -3 AS SEQ UNION ALL
SELECT '     WHERE '                 , -2 AS SEQ 
ORDER BY 2
;

저작자표시

'(DB) MS SQL > SQL-Select' 카테고리의 다른 글

MSSQL-Connect By. 조직도 조회 (0)	2017.04.18
MS SQL.Select-Pivot. UnPivot (0)	2017.02.09
MS SQL.Select-java vo자동생성, procedure arg 자동생성 (0)	2017.02.09
MS SQL.SELECT-레코드를 한 컬럼으로 만들기 (0)	2017.02.09

Posted by 농부지기

,

MS SQL.SELECT-레코드를 한 컬럼으로 만들기

(DB) MS SQL/SQL-Select 2017. 2. 9. 17:02

[ MS SQL.SELECT-레코드를 한 컬럼으로 만들기 ]

저작자표시

'(DB) MS SQL > SQL-Select' 카테고리의 다른 글

MSSQL-Connect By. 조직도 조회 (0)	2017.04.18
MS SQL.Select-Pivot. UnPivot (0)	2017.02.09
MS SQL.Select-java vo자동생성, procedure arg 자동생성 (0)	2017.02.09
MS SQL.Select-CRUD 문장 자동 만들어주기 (0)	2017.02.09

Posted by 농부지기

,

일	월	화	수	목	금	토
						1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28

농부지기

'(DB) MS SQL/SQL-Select'에 해당되는 글 5건

MSSQL-Connect By. 조직도 조회

'(DB) MS SQL > SQL-Select' 카테고리의 다른 글

MS SQL.Select-Pivot. UnPivot

'(DB) MS SQL > SQL-Select' 카테고리의 다른 글

MS SQL.Select-java vo자동생성, procedure arg 자동생성

'(DB) MS SQL > SQL-Select' 카테고리의 다른 글

MS SQL.Select-CRUD 문장 자동 만들어주기

'(DB) MS SQL > SQL-Select' 카테고리의 다른 글

MS SQL.SELECT-레코드를 한 컬럼으로 만들기

'(DB) MS SQL > SQL-Select' 카테고리의 다른 글

카테고리

태그목록

최근에 올라온 글

최근에 달린 댓글

최근에 받은 트랙백

글 보관함

달력

링크

티스토리툴바