MSSQL 자주 사용하는 쿼리

용도쿼리비고

특정 테이블이 포함된 SP 목록 조회	SELECT OBJECT_NAME(object_id), OBJECT_DEFINITION(object_id) FROM sys.procedures WITH (NOLOCK) WHERE OBJECT_DEFINITION(object_id) LIKE '%찾을테이블명%'
특정 단어가 포함된 SP 목록 조회	SELECT ROUTINE_NAME          FROM INFORMATION_SCHEMA.ROUTINES     WHERE ROUTINE_DEFINITION LIKE '%Search_Text%'         AND ROUTINE_TYPE='PROCEDURE'         order by ROUTINE_NAME
	SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%SearchText%' order by ROUTINE_NAME
특정 테이블의 Comment 및 스키마 정보 조회	SELECT A.TABLE_NAME, C.VALUE AS TABLE_COMMENT, A.COLUMN_NAME, A.DATA_TYPE, ISNULL(CAST(A.CHARACTER_MAXIMUM_LENGTH AS VARCHAR), CAST(A.NUMERIC_PRECISION AS VARCHAR) + ',' + CAST(A.NUMERIC_SCALE AS VARCHAR)) AS COLUMN_LENGTH, A.COLUMN_DEFAULT, A.IS_NULLABLE, B.VALUE AS COLUM_COMMENT 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 A.TABLE_NAME = 'Search_Text'
특정 컬럼명이 포함된 테이블 목록 조회	SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%SearchText%' order by TABLE_NAME
	--base_table를 기준으로 target_table을 비교하여 갱신 MERGE INTO target_table AS a USING (SELECT columnA,              columnB,              columnC from base_table) AS b  -- 비교할 조건을 작성 ON (a.columnA = b.columnA) --데이터가 존재 하는 경우 업데이트(UPDATE) WHEN MATCHED THEN UPDATE SET columnA = b.columnA,            columnB = b.columnB,            columnC = b.columnC --데이터가 존재하지 않는 경우 추가(INSERT) WHEN NOT MATCHED THEN INSERT (columnA,         columnB,         columnC) VALUES (b.columnA,         b.columnB,         b.columnC)
CTE를 이용한 재귀	WITH CTE AS ( -- Base SELECT MIdx, MenuCd, MenuNm, ParentsMenuId, 0 AS Level FROM TB_MENU_INFO WITH (NOLOCK) WHERE ParentsMenuId IS NULL UNION ALL -- Recursive SELECT A.MIdx, A.MenuCd, A.MenuNm,A.ParentsMenuId, B.Level + 1 AS Level FROM TB_MENU_INFO AS A WITH (NOLOCK) INNER JOIN CTE AS B ON A.ParentsMenuId = B.MIdx ) SELECT * FROM CTE ORDER BY LEVEL ASC;
OPENQUERY	SELECT * FROM OPENQUERY( [NAIS_REAL], 'SELECT BASIS_DT, BASIS_HOUR, GAME_CD, GAME_NM, USER_DIV_CD, MAIN_FLAG, TOTAL_REG_USER_CNT, NEW_REG_USER_CNT, DAU_CNT, BU_CNT, PMT_AMT, MAX_CCU_CNT, AVG_CCU_CNT, DAY_MAX_CCU_CNT, DW_LOAD_DTTM FROM BIPORTAL.DBO.DM_MAIN_H_AGG WITH (NOLOCK)' );
PIVOT	SELECT userIp ,Controller ,endTime ,method ,params ,requestType ,resUri , screenNm ,startTime ,A.userId ,B.UserName ,userIp FROM ( SELECT event_id , mapped_key , mapped_value FROM logging_event_property WITH (NOLOCK) ) AS A PIVOT ( MIN(mapped_value) FOR mapped_key IN ( [Controller] ,[endTime] ,[method] ,[params] ,[requestType] ,[resUri] ,[screenNm] ,[startTime] ,[userId] ,[userIp] ) ) A LEFT JOIN MISUser AS B WITH (NOLOCK) ON A.userId = B.UserId WHERE A.userId <> ''
데이버테이스 축소	USE [데이터베이스명] GO ALTER DATABASE [데이터베이스명] SET RECOVERY SIMPLE; GO DBCC SHRINKFILE ([데이터베이스명]_LOG,10) GO ALTER DATABASE [데이터베이스명] SET RECOVERY FULL; GO
identity on/off	SET IDENTITY_INSERT 테이블 ON  SET IDENTITY_INSERT 테이블 OFF