테이블이나 컬럼이 사용중인지 트레이스 하고 싶어요

USE MASTER ;
GO 

IF OBJECT_ID('dbo.usp_query_check') IS NOT NULL
    DROP PROCEDURE dbo.usp_query_check;
GO
CREATE PROCEDURE dbo.usp_query_check
    @keywords   NVARCHAR(MAX) = NULL,          -- 콤마로 구분된 검색 문자 (예: N'birthMonthDate,CFT_MARKET_ITEM_OTN')
    @action     NVARCHAR(10)  = N'start',       -- 'start' = 삭제 후 재생성 + 시작, 'stop' = 정지
    @filepath   NVARCHAR(260) = N'D:\DBA\XERESULT\querycheck.xel'
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql            NVARCHAR(MAX);
    DECLARE @sessionName    SYSNAME = N'querycheck';

    ---------------------------------------------------------------
    -- STOP 처리
    ---------------------------------------------------------------
    IF (LOWER(@action) = N'stop')
    BEGIN
        IF EXISTS (SELECT 1 FROM sys.dm_xe_sessions WHERE name = @sessionName)
        BEGIN
            SET @sql = N'ALTER EVENT SESSION ' + QUOTENAME(@sessionName)
                     + N' ON SERVER STATE = STOP;';
            EXEC sp_executesql @sql;
            PRINT N'Event session [' + @sessionName + N'] stopped.';
        END
        ELSE
            PRINT N'Event session [' + @sessionName + N'] is not running.';
        RETURN;
    END

    ---------------------------------------------------------------
    -- START 처리 : 검색 문자 필수
    ---------------------------------------------------------------
    IF (@keywords IS NULL OR LTRIM(RTRIM(@keywords)) = N'')
    BEGIN
        RAISERROR(N'@keywords is required for start action (comma separated).', 16, 1);
        RETURN;
    END

    ---------------------------------------------------------------
    -- 콤마 분리 후 컬럼별 WHERE 절 동적 생성
    -- (statement 대상용 / batch_text 대상용 각각)
    ---------------------------------------------------------------
    DECLARE @whereStmt  NVARCHAR(MAX) = N'';
    DECLARE @whereBatch NVARCHAR(MAX) = N'';

    ;WITH parts AS (
        SELECT LTRIM(RTRIM(value)) AS kw
        FROM STRING_SPLIT(@keywords, N',')
        WHERE LTRIM(RTRIM(value)) <> N''
    )
    SELECT
        @whereStmt = STRING_AGG(
            N'[sqlserver].[like_i_sql_unicode_string]([statement],N''%'
            + REPLACE(kw, N'''', N'''''') + N'%'')', N' OR '),
        @whereBatch = STRING_AGG(
            N'[sqlserver].[like_i_sql_unicode_string]([batch_text],N''%'
            + REPLACE(kw, N'''', N'''''') + N'%'')', N' OR ')
    FROM parts;

    IF (@whereStmt IS NULL OR @whereStmt = N'')
    BEGIN
        RAISERROR(N'No valid keywords parsed from @keywords.', 16, 1);
        RETURN;
    END

    ---------------------------------------------------------------
    -- 기존 세션 삭제
    ---------------------------------------------------------------
    IF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = @sessionName)
    BEGIN
        SET @sql = N'DROP EVENT SESSION ' + QUOTENAME(@sessionName) + N' ON SERVER;';
        EXEC sp_executesql @sql;
    END

    ---------------------------------------------------------------
    -- 세션 생성
    ---------------------------------------------------------------
    SET @sql =
        N'CREATE EVENT SESSION ' + QUOTENAME(@sessionName) + N' ON SERVER ' + NCHAR(13) +
        N'ADD EVENT sqlserver.sp_statement_completed(SET collect_statement=(1)' + NCHAR(13) +
        N'    ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.username)' + NCHAR(13) +
        N'    WHERE (' + @whereStmt + N')),' + NCHAR(13) +
        N'ADD EVENT sqlserver.rpc_completed(SET collect_statement=(1)' + NCHAR(13) +
        N'    ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_principal_name,sqlserver.session_nt_username,sqlserver.username)' + NCHAR(13) +
        N'    WHERE (' + @whereStmt + N')),' + NCHAR(13) +
        N'ADD EVENT sqlserver.sql_batch_completed(SET collect_batch_text=(1)' + NCHAR(13) +
        N'    ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.username)' + NCHAR(13) +
        N'    WHERE (' + @whereBatch + N'))' + NCHAR(13) +
        N'ADD TARGET package0.event_file(SET filename=N''' + REPLACE(@filepath, N'''', N'''''') + N''',max_file_size=(100))' + NCHAR(13) +
        N'WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=1 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF);';

    EXEC sp_executesql @sql;

    ---------------------------------------------------------------
    -- 시작
    ---------------------------------------------------------------
    SET @sql = N'ALTER EVENT SESSION ' + QUOTENAME(@sessionName)
             + N' ON SERVER STATE = START;';
    EXEC sp_executesql @sql;

    PRINT N'Event session [' + @sessionName + N'] (re)created and started.';
    PRINT N'Keywords: ' + @keywords;
END
GO

EXEC dbo.usp_query_check
     @keywords = N'birthMonthDate, CFT_MARKET_ITEM_OTN',
     @action   = N'start',
     @filepath = N'D:\DBA\XERESULT\querycheck.xel';


EXEC dbo.usp_query_check @action = N'stop';

 

 

 

생성하면 아래 스크립트를 자동으로 만들어준다.

CREATE EVENT SESSION [querycheck] ON SERVER 
ADD EVENT sqlserver.rpc_completed(SET collect_statement=(1)
    ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_principal_name,sqlserver.session_nt_username,sqlserver.username)
    WHERE ([sqlserver].[like_i_sql_unicode_string]([statement],N'%MonthDates%') OR [sqlserver].[like_i_sql_unicode_string]([statement],N'%ITEM_OTN%'))),
ADD EVENT sqlserver.sp_statement_completed(SET collect_statement=(1)
    ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.username)
    WHERE ([sqlserver].[like_i_sql_unicode_string]([statement],N'%MonthDates%') OR [sqlserver].[like_i_sql_unicode_string]([statement],N'%ITEM_OTN%'))),
ADD EVENT sqlserver.sql_batch_completed(SET collect_batch_text=(1)
    ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.username)
    WHERE ([sqlserver].[like_i_sql_unicode_string]([batch_text],N'%MonthDates%') OR [sqlserver].[like_i_sql_unicode_string]([batch_text],N'%ITEM_OTN%')))
ADD TARGET package0.event_file(SET filename=N'D:\DBA\XERESULT\querycheck.xel',max_file_size=(100))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=1 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO