WITH kullanilmayan as ( select object_schema_name(t.object_id) + '.' + t.name as TableName from sys.dm_db_index_usage_stats i right outer join sys.tables t on (t.object_id = i.object_id) group by object_schema_name(t.object_id) + '.' + t.name having sum(isnull(i.user_seeks,0)+isnull(i.system_seeks,0)+isnull(i.user_scans,0)+ isnull(i.system_scans,0) + isnull(i.user_lookups,0) + isnull(i.system_lookups,0)+ isnull(i.user_updates,0)+ isnull(i.system_updates,0))=0 ) ,boyut as ( select TableName, sum(RowCounts) RowCounts,sum(TotalSpaceKB) TotalSpaceKB,sum(UsedSpaceKB) UsedSpaceKB, sum(UnusedSpaceKB) UnusedSpaceKB from (SELECT object_schema_name(t.object_id) + '.' + t.name AS TableName, s.Name AS SchemaName, p.rows AS RowCounts, SUM(a.total_pages) * 8 AS TotalSpaceKB, SUM(a.used_pages) * 8 AS UsedSpaceKB, (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB FROM sys.tables t INNER JOIN sys.indexes i ON t.object_id = i.object_id INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE t.NAME NOT LIKE 'dt%' AND t.is_ms_shipped = 0 AND i.object_id > 255 GROUP BY object_schema_name(t.object_id) + '.' + t.name, s.Name, p.Rows ) as t group by TableName ) SELECT * FROM kullanilmayan k JOIN boyut b on k.TableName = b.TableName
