MS SQL 미사용 테이블 확인 SQL

MS-SQL 미사용 테이블 확인 SQL

SQL Server의 SYS.dm_db_index_usage_stats 뷰의 최종접근일시 정보(last_user_scan, last_user_update 등)를 기반으로 미사용 Object를 식별

SELECT  "?" AS dbname,
        A.object_id,
        A.objectname,
        A.rowcnt,
        A.createdate,
        A.modifydate,
        B.database_id,
        B.object_id,
        B.last_user_seek,
        B.last_user_scan,
        B.last_user_lookup,
        B.last_user_update,
        B.last_system_scan
FROM    (
            SELECT 
                    A.object_id, -- 오브젝트 아이디
                    object_name(A.object_id) AS objectname, -- 오브젝트 이름
                    max(C.row_count) as rowcnt, -- 오브젝트 행 수
                    max(B.create_date) as createdate, -- 오브젝트 생성일
                    max(B.modify_date) as modifydate -- 오브젝트 마지막 수정일
            FROM    sys.indexes AS A
                    INNER JOIN sys.objects AS B ON A.object_id = B.object_id
                    INNER JOIN sys.dm_db_partition_stats AS C ON C.object_id = B.object_id
            WHERE
                    B.type IN (''U'', ''V'')
            GROUP BY
                    A.object_id
        ) AS A LEFT JOIN (
            SELECT  database_id, -- 데이터베이스 아이디
                    object_id, -- 오브젝트 아이디
                    MAX(last_user_seek) AS last_user_seek, -- 마지막 Seek 일
                    MAX(last_user_scan) AS last_user_scan, -- 마지막 Scan 일
                    MAX(last_user_lookup) AS last_user_lookup, -- 마지막 lookup 일
                    MAX(last_user_update) AS last_user_update, -- 마지막 update 일
                    MAX(last_system_scan) AS last_system_scan -- 마지막 시스템 Scan
            FROM    sys.dm_db_index_usage_stats
            WHERE   db_name(database_id) = "?" -- 해당 데이터베이스
            GROUP BY
                    database_id,
                    object_id
        ) AS B ON A.object_id = B.object_id
WHERE
        B.object_id is null
        AND "?" != ''tempdb''