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

(DB) MS SQL/SQL-Select

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

농부지기 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  ;

저작자표시 (새창열림)