아래의 쿼리를 오라클에서 MSSQL,로 변환 할려고 하는데 업렵네됴..ㅠ.ㅠ
고수분들의 도움 요청 드립니다. ...
DECLARE
V_TABLE_NAME_ENG VARCHAR2(200);
V_TABLE_NAME_KOR VARCHAR2(200);
V_COULUMN_NAME_KOR VARCHAR2(200);
V_COULUMN_NAME_ENG VARCHAR2(200);
V_TEXT_TABLE VARCHAR2(200);
V_TEXT VARCHAR2(200);
V_TEXT_TWO VARCHAR2(200);
V_TEXT_THREE VARCHAR2(200);
V_TEXT_FORE VARCHAR2(400);
V_YP_KG VARCHAR2(200);
V_TS_MPA VARCHAR2(200);
V_TS_KG VARCHAR2(200);
V_EL VARCHAR2(200);
V_NVAL VARCHAR2(200);
V_THK VARCHAR2(200);
V_MQL_TSTP_NO VARCHAR2(200);
V_MQL_TSTP_GTH_PRT VARCHAR2(200);
V_PLNT_TP VARCHAR(200);
V_TEXT_SIN_QT VARCHAR(200);
V_TEXT_DBO VARCHAR(200);
V_TEXT_COLUMN VARCHAR(200);
V_TEXT_SENTENCT VARCHAR(200);
CURSOR C1 IS
SELECT DECODE(ROWNUM,1,'TABLE1'
,2,'TABLE12'
,3,'OIS_ARV_DELIVERY_SHIP'
,4,'OIS_BERTH'
,5,'OIS_PUBLIC_CODE'
,6,'OIS_SHIP_INFO'
,7,'OIS_SHIP_INFO_NEW'
,8,'OIS_SHIP_INFO_REMARK_HISTORY'
,9,'OIS_TERMINAL_FEEDBACK'
,10,'OIS_VETTING_ASSESSMENT'
,11,'OIS_VETTING_REQUEST'
--,12,'TB_MD_G_LOG_IF'
-- ,14,'TB_MD_I_INOUT_ATTACH'
-- ,15,'TB_MD_I_INOUT_ITEM'
-- ,16,'TB_MD_I_INOUT_LOAD'
-- ,17,'TB_MD_I_INOUT_MST'
-- ,18,'TB_MD_L_INOUT_ORDER'
-- ,19,'TB_MD_L_MOVE_PLAN'
-- ,20,'TB_MD_L_MOVE_RESULT'
)
CODE_T
FROM DUAL CONNECT BY LEVEL < = 11;
BEGIN
V_TEXT := 'EXEC sp_addextendedproperty ''MS_Description''';
V_TEXT_TWO := ',';
V_TEXT_THREE := '''USER''';
V_TEXT_FORE := '''TABLE''';
V_TEXT_SIN_QT := '''';
V_TEXT_DBO :='dbo';
V_TEXT_TABLE := '''TABLE''';
V_TEXT_COLUMN := '''COLUMN''';
FOR C1_REC IN C1 LOOP
V_MQL_TSTP_NO := C1_REC.CODE_T;
-- DBMS_OUTPUT.PUT_LINE(V_MQL_TSTP_NO);
--1.TABLE 코멘트 GET
SELECT A.TABLE_NAME,B.COMMENTS INTO V_TABLE_NAME_ENG, V_TABLE_NAME_KOR
FROM ALL_TABLES A, ALL_TAB_COMMENTS B
WHERE A.TABLE_NAME = B.TABLE_NAME
AND A.TABLE_NAME = V_MQL_TSTP_NO
AND A.OWNER = 'SPIRITS'
AND A.OWNER = B.OWNER;
--CONNECT BY LEVEL < = 1
DBMS_OUTPUT.PUT_LINE('-------TABLE--------');
V_TEXT_SENTENCT := V_TEXT||V_TEXT_TWO||V_TEXT_SIN_QT||V_TABLE_NAME_KOR||V_TEXT_SIN_QT||V_TEXT_TWO||V_TEXT_THREE||V_TEXT_TWO||V_TEXT_DBO||V_TEXT_TWO||V_TEXT_FORE||V_TEXT_TWO||V_TABLE_NAME_ENG;
DBMS_OUTPUT.PUT_LINE(V_TEXT_SENTENCT);
--2.COLUMN 코멘트 GET
DECLARE
CURSOR D1 IS
SELECT A.COLUMN_NAME AS COLUMN_NAME,B.COMMENTS AS COMMENTS
FROM ALL_TAB_COLUMNS A
,ALL_COL_COMMENTS B
WHERE A.TABLE_NAME = B.TABLE_NAME
AND A.COLUMN_NAME = B.COLUMN_NAME
AND A.TABLE_NAME = V_TABLE_NAME_ENG
AND A.OWNER = 'SPIRITS'
AND A.OWNER = B.OWNER
ORDER BY A.TABLE_NAME, A.COLUMN_ID;
BEGIN
DBMS_OUTPUT.PUT_LINE('-----COLUMN--------');
FOR D1_REC IN D1 LOOP
DBMS_OUTPUT.PUT_LINE(V_TEXT||V_TEXT_TWO||V_TEXT_SIN_QT||D1_REC.COMMENTS||V_TEXT_SIN_QT||V_TEXT_TWO||V_TEXT_THREE||V_TEXT_TWO||V_TEXT_DBO||V_TEXT_TWO||V_TEXT_TABLE||V_TEXT_TWO||V_TABLE_NAME_ENG||V_TEXT_TWO
||V_TEXT_COLUMN||V_TEXT_TWO||D1_REC.COLUMN_NAME
);
-- DBMS_OUTPUT.PUT_LINE(D1_REC.COMMENTS);
END LOOP;
END;
END LOOP;
END;