푸팟퐁쿼리

특정 테이블의 마지막 접근 시간(Access Time)을 조회하는 쿼리입니다. 해당 테이블이 사용되고 있는지, 미사용 테이블인지 대략적으로 확인할 때 사용할 수 있습니다. select DB_NAME(usage.database_id) AS db_name, schema_name, table_name, max(last_access) as last_access from( select sta.database_id as database_id, schema_name(schema_id) as schema_name, name as table_name, (select max(last_access) from (values(last_user_seek), (last_user_scan), (last_user_lookup), (l..

table_schema, table_name 컬럼에 특정 스키마/테이블 명을 넣어 특정 테이블 용량 조회도 가능합니다. SELECT TABLE_NAME AS 'Tables', round(((data_length) / 1024 / 1024), 2) 'Data size(MB)', round(((index_length) / 1024 / 1024), 2) 'Index size(MB)' FROM information_schema.TABLES --WHERE table_schema = "[schema]" --AND table_name = '[table]' ORDER BY (data_length + index_length) DESC;

1. 특정 DB의 인덱스 용량만 조회할 경우 SELECT OBJECT_SCHEMA_NAME(i.object_id) AS SchemaName, OBJECT_NAME(i.OBJECT_ID) AS TableName, i.name AS IndexName, i.index_id AS IndexID, CASE i.index_id WHEN 1 THEN 'Clustered Index' ELSE 'NonClustered Index' END AS IndexType, SUM(au.used_pages) * 8 AS 'Indexsize(KB)' FROM sys.indexes AS i INNER JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.ind..

SELECT OBJECT_SCHEMA_NAME(a2.object_id) AS SchemaName, a2.name AS TableName, a1.rows as [RowCount], CAST(ROUND(((a1.reserved + ISNULL(a4.reserved,0)) * 8) / 1024.00 / 1024.00, 2) AS NUMERIC(36, 2)) AS ReservedSize_GB, CAST(ROUND(a1.data * 8 / 1024.00 / 1024.00 , 2) AS NUMERIC(36, 2)) AS DataSize_GB, CAST(ROUND((CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) ..