-
MSSQL 자주 사용하는 쿼리카테고리 없음 2021. 7. 24. 21:06
용도쿼리비고
특정 테이블이 포함된 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_NAMESELECT *
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;
GOidentity on/off SET IDENTITY_INSERT 테이블 ON
SET IDENTITY_INSERT 테이블 OFF댓글