https://www.garamx.com/dokuwiki/ 2026-05-06T13:38:21+00:00 https://www.garamx.com/dokuwiki/ https://www.garamx.com/dokuwiki/lib/tpl/darkblue/images/favicon.ico text/html 2025-04-15T10:05:18+00:00 Anonymous (anonymous@undisclosed.example.com) https://www.garamx.com/dokuwiki/doku.php?id=sqlserver:compatibility&rev=1744711518&do=diff Compatibility 확인 select name, compatibility_level , version_name = CASE compatibility_level WHEN 65 THEN 'SQL Server 6.5' WHEN 70 THEN 'SQL Server 7.0' WHEN 80 THEN 'SQL Server 2000' WHEN 90 THEN 'SQL Server 2005' WHEN 100 THEN 'SQL Server 2008/R2' WHEN 110 THEN 'SQL Server 2012' WHEN 120 THEN 'SQL Server 2014' WHEN 130 THEN 'SQL Server 2016' WHEN 140 THEN 'SQL Server 2017' WHEN 150 THEN 'SQL Server 2019' ELSE 'new unknown - '+CONVERT(varchar(10… text/html 2025-04-15T10:05:18+00:00 Anonymous (anonymous@undisclosed.example.com) https://www.garamx.com/dokuwiki/doku.php?id=sqlserver:connectby&rev=1744711518&do=diff Oracle의 Connect By와 유사하게 Tree구현 WITH h$cte AS ( SELECT 1 AS LEVEL, A.*, dbo.xf_nvl_c( dbo.F_FRM_GET_TERM('KO', 'MENU_NM', a.MENU_ID), a.menu_nm ) as title, dbo.F_FRM_IS_FAV_MENU(A.menu_id, 1) as is_favorite, (SELECT b.OBJECT_DISPLAY_NM FROM FRM_EXECUTABLE_OBJECT b WHERE b.object_nm = a.object_id AND b.company_cd = a.company_cd) as object_nm, (SELECT attr… text/html 2025-04-15T10:05:18+00:00 Anonymous (anonymous@undisclosed.example.com) https://www.garamx.com/dokuwiki/doku.php?id=sqlserver:cursor&rev=1744711518&do=diff CURSOR사용하기 MS SQL Server에서는 For-Cursor문이 지원되지 않는다.(무식하게도..) @@FETCH_STATUS 이 함수는 현재 연결에서 연 모든 커서에 실행된 마지막 커서 FETCH 문의 상태를 반환합니다. @@FETCH_STATUS는 연결의 모든 커서에 전역으로 적용되므로 신중히 사용하세요. text/html 2025-04-15T10:05:18+00:00 Anonymous (anonymous@undisclosed.example.com) https://www.garamx.com/dokuwiki/doku.php?id=sqlserver:dblink&rev=1744711518&do=diff DB Link -- Select SELECT * FROM OPENQUERY(LINKED_NAME, 'SELECT * FROM test_tbl'); -- Insert INSERT OPENQUERY(LINKED_NAME, 'SELECT ID, NAME FROM test_tbl ') VALUES (1, 'name'); INSERT OPENQUERY(MYORACLE, 'SELECT * FROM emp ') select 1, '10000', '테스트' ; -- Update UPDATE OPENQUERY(LINKED_NAME, 'SELECT NAME FROM test_tbl WHERE ID = 1') SET NAME = 'value'; -- Delete DELETE FROM OPENQUERY(LINKED_NAME, 'SELECT ID FROM test_tbl WHERE ID = 1'); --참조프로시저 : SP_DEBIS_INSERT --DB 호환 --sp_helpdb… text/html 2025-04-15T10:05:18+00:00 Anonymous (anonymous@undisclosed.example.com) https://www.garamx.com/dokuwiki/doku.php?id=sqlserver:find_procedure&rev=1744711518&do=diff 프로시저소스찾기 프로시저에서 소스찾기 SELECT OBJECT_NAME(object_id), OBJECT_DEFINITION(object_id) FROM sys.procedures WHERE OBJECT_DEFINITION(object_id) LIKE '%H_HUMAN.FG_PERSON%' ; SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%PHM_POS_GRD_CD%' AND ROUTINE_TYPE='PROCEDURE' text/html 2025-04-15T10:05:18+00:00 Anonymous (anonymous@undisclosed.example.com) https://www.garamx.com/dokuwiki/doku.php?id=sqlserver:home&rev=1744711518&do=diff MS SQL Server MS SQL Server * Compatibility확인 * SSMS 설정 * 테이블복사 * TABLE INFO * Cursor사용하기 * Rename Column * INFORMATION_SCHEMA * Oracle의 Connect By구현 * Procedure소스찾기 * Ordering * Try Catch * 데이터복구 * Update Join * Oracle의 LAST_DAY * 재귀 CTE(Common Table Expression) * MSSQL DB Link * SEAMLESS * exec sp_lock2 * Kill Session 작동을 확인 못함 text/html 2025-04-15T10:05:18+00:00 Anonymous (anonymous@undisclosed.example.com) https://www.garamx.com/dokuwiki/doku.php?id=sqlserver:information_schema&rev=1744711518&do=diff INFORMATION_SCHEMA TABLES select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE from INFORMATION_SCHEMA.TABLES VIEWS SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, VIEW_DEFINITION, CHECK_OPTION, IS_UPDATABLE FROM INFORMATION_SCHEMA.VIEWS text/html 2025-04-15T10:05:18+00:00 Anonymous (anonymous@undisclosed.example.com) https://www.garamx.com/dokuwiki/doku.php?id=sqlserver:kill_session&rev=1744711518&do=diff Kill the Session 현재 실행중인 쿼리 현재 수행중인 쿼리를 찾아서 어떠한 쿼리가 날아가고 있는지만 확인해도 DBA라면 대충은 무거운 쿼리인지 얼마나 걸릴 쿼리인지, 죽여야할 쿼리인지 정도는 예측할 수 있다, 따라서 현재 실행중인 쿼리를 찾아 먼저 어떤 쿼리인지 확인 하는 것이 중요하다. text/html 2025-04-15T10:05:18+00:00 Anonymous (anonymous@undisclosed.example.com) https://www.garamx.com/dokuwiki/doku.php?id=sqlserver:last_day&rev=1744711518&do=diff Oracle function last_day SQL Server로 변경 declare @date_t datetime declare @d datetime set @date_t = '20200820' set @d = DATEADD(m, 1, @date_t) set @d = DATEADD(d, - DAY(@d), @d) select @date_t, @d create or alter function dbo.XF_LAST_DAY( @ad_date datetime ) return datetime as BEGIN DECLARE @d datetime set @d = DATEADD(m, 1, @ad_date) return DATEADD(d, - DAY(@d), @d) END text/html 2025-04-15T10:05:18+00:00 Anonymous (anonymous@undisclosed.example.com) https://www.garamx.com/dokuwiki/doku.php?id=sqlserver:orderby_rank&rev=1744711518&do=diff Ordering SELECT FRM_UNIT_STD_HIS_ID, A.FRM_UNIT_STD_MGR_ID, KEY_CD1, KEY_CD2, KEY_CD3, KEY_CD4, KEY_CD5, CD1, CD2, CD3, CD4, CD5, ETC_CD1, ETC_CD2, ETC_CD3, ETC_CD4, ETC_CD5, STA_YMD, END_YMD, A.NOTE, CASE WHEN EDIT_FORMAT_K1 = '2' THEN ROW_NUMBER() OVER ( ORDER BY dbo.XF_TO_NUMBER(A.KEY_CD1)) ELSE ROW_NUMBER() OVER ( ORDER BY KEY_CD1) END AS ORDERING… text/html 2025-04-15T10:05:18+00:00 Anonymous (anonymous@undisclosed.example.com) https://www.garamx.com/dokuwiki/doku.php?id=sqlserver:recovery&rev=1744711518&do=diff <https://ajh322.tistory.com/98> text/html 2025-04-15T10:05:18+00:00 Anonymous (anonymous@undisclosed.example.com) https://www.garamx.com/dokuwiki/doku.php?id=sqlserver:recursive_cte&rev=1744711518&do=diff 재귀 CTE( Recursive Common Table Expression ) 재귀 공통 테이블 표현식 CTE는 이해하기 쉽고 읽기 쉽고 유지 관리가 쉬운 재귀 쿼리 를 작성하는 메커니즘을 제공 합니다 . CTE 이전에는 재귀 쿼리를 작성하는 유일한 메커니즘은 재귀 함수 또는 저장 프로 시저를 사용하는 것입니다. 그리고 이러한 재귀 함수 또는 저장 프로시 저는 최대 32 개 수준의 재귀 만 지원합니다. text/html 2025-04-15T10:05:18+00:00 Anonymous (anonymous@undisclosed.example.com) https://www.garamx.com/dokuwiki/doku.php?id=sqlserver:rename_column&rev=1744711518&do=diff Rename Column EXEC sp_RENAME 'TableName.OldColumnName' , 'New Column Name', 'COLUMN' text/html 2025-04-15T10:05:18+00:00 Anonymous (anonymous@undisclosed.example.com) https://www.garamx.com/dokuwiki/doku.php?id=sqlserver:seamless&rev=1744711518&do=diff UPDATE REP_INSUR_ACC SET end_ymd=(select dbo.XF_NVL_D(dbo.XF_DATEADD(min(calc_ymd),-1) , X.end_ymd ) FROM REP_INSUR_ACC as A WHERE A.calc_ymd > X.calc_ymd AND A.company_cd = X.company_cd AND A.ctz_no = X.ctz_no AND A.emp_id = X.emp_id) FROM REP_INSUR_ACC as X WHERE 1=1 AND X.company_cd = 'E' AND X.ctz_no = 'qzDppshPl9Oti+cRoA2hRQ==' AND X.emp_id = '60487' ; text/html 2025-04-15T10:05:18+00:00 Anonymous (anonymous@undisclosed.example.com) https://www.garamx.com/dokuwiki/doku.php?id=sqlserver:sp_lock2&rev=1744711518&do=diff Procedure sp_lock2 SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROC [dbo].[sp_lock2] ( @dbname sysname = NULL, @spid int = NULL ) AS /************************************************************************************************* Copyright © 2001 Narayana Vyas Kondreddi. All rights reserved. Purpose: To display detailed lock information Written by: Narayana Vyas Kondreddi http://vyaskn.tripod.com Tested on: SQL Server 7.0 and SQL S… text/html 2025-04-15T10:05:18+00:00 Anonymous (anonymous@undisclosed.example.com) https://www.garamx.com/dokuwiki/doku.php?id=sqlserver:sql_server_db_link&rev=1744711518&do=diff DB Link 연결된 서버 - MSSQL 연결된 서버란? MSSQL 은 연결된서버 기능을 제공하는데 이를 이용하면 다른 네트워크의 데이터베이스를 원격으로 접속하여 사용할 수 있도록 해줍니다. - MSSQL 연결된 서버 생성 text/html 2025-04-15T10:05:18+00:00 Anonymous (anonymous@undisclosed.example.com) https://www.garamx.com/dokuwiki/doku.php?id=sqlserver:ssms&rev=1744711518&do=diff SSMS설정 SSMS에서 Comment보기 컴퓨터\HKEY_CURRENT_USER\Software\Microsoft\SQL Server Management Studio\14.0\DataProject 에 있는 SSVPropViewColumnsSQL70 SSVPropViewColumnsSQL80 의 기본값인 1,2,6 값을 1,2,6,7,17 로 변경해주고 SSMS를 재구동 하면 위의 사진처럼 Description(설명) 항목이 보여진다. text/html 2025-04-15T10:05:18+00:00 Anonymous (anonymous@undisclosed.example.com) https://www.garamx.com/dokuwiki/doku.php?id=sqlserver:table_copy&rev=1744711518&do=diff MS-SQL 테이블 복사 1. 테이블을 생성하면서 테이블의 데이타 복사 select * into 생성될테이블명 from 원본테이블명 테이블 구조만 복사하겠다면 select * into 생성될테이블명 from 원본테이블명 where 1=2 text/html 2025-04-15T10:05:18+00:00 Anonymous (anonymous@undisclosed.example.com) https://www.garamx.com/dokuwiki/doku.php?id=sqlserver:table_info&rev=1744711518&do=diff Table Information CREATE OR ALTER PROCEDURE [dbo].[P_INFO_TABLE] @table_name sysname, @table_schema sysname = null AS SELECT CASE WHEN A.ORDINAL_POSITION = 1 THEN A.TABLE_NAME ELSE '' END AS TABLE_NAME, CASE WHEN A.ORDINAL_POSITION = 1 THEN C.TABLE_COMMENT ELSE '' END AS TABLE_COMMENT, A.COLUMN_NAME, B.COLUMN_COMMENT, A.DATA_TYPE, ISNULL(CAST(A.CHARACTER_MAXIMUM_LENGTH AS VARCHAR), ISNULL(CAST(A.NUMERIC_PRECISION AS VARCHAR… text/html 2025-04-15T10:05:18+00:00 Anonymous (anonymous@undisclosed.example.com) https://www.garamx.com/dokuwiki/doku.php?id=sqlserver:try_catch&rev=1744711518&do=diff TRY CATCH TRY...CATCH 구문은 심각도가 10을 넘으며 데이터베이스 연결을 닫지 않는 모든 실행 오류를 catch합니다. TRY 블록 다음에는 곧바로 연결된 CATCH 블록이 이어져야 합니다. END TRY와 BEGIN CATCH 문 사이에 다른 문을 포함시키면 구문 오류가 발생합니다.