TÜM TABLOLARIN KAPLADIĞI ALANI BULMA (GET TABLE SIZE ON DISK)

TABLOLARIN DİSKTE KAPLADIĞI ALANI BULMA

USE dtb_fiyat

GO

create table #TableSize (

    Name varchar(255),

    [rows] int,

    reserved varchar(255),

    data varchar(255),

    index_size varchar(255),

    unused varchar(255))

create table #ConvertedSizes (

    Name varchar(255),

    [rows] int,

    reservedKb int,

    dataKb int,

    reservedIndexSize int,

    reservedUnused int)

EXEC sp_MSforeachtable @command1="insert into #TableSize

EXEC sp_spaceused '?'"

insert into #ConvertedSizes (Name, [rows], reservedKb, dataKb, reservedIndexSize, reservedUnused)

select name, [rows], 

SUBSTRING(reserved, 0, LEN(reserved)-2), 

SUBSTRING(data, 0, LEN(data)-2), 

SUBSTRING(index_size, 0, LEN(index_size)-2), 

SUBSTRING(unused, 0, LEN(unused)-2)

from #TableSize

select * from #ConvertedSizes

order by reservedKb desc

drop table #TableSize

drop table #ConvertedSizes

HER BİR İNDEX TEK TEK NE KADAR ALAN KAPLADIĞINI GÖRME

SELECT
    OBJECT_NAME(i.OBJECT_ID) AS TableName,
    i.name AS IndexName,
    i.index_id AS IndexID,
    8 * SUM(a.used_pages) AS 'Indexsize(KB)'
FROM
    sys.indexes AS i JOIN 
    sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id JOIN 
    sys.allocation_units AS a ON a.container_id = p.partition_id
where [i].[is_primary_key] = 0 -- fix for size discrepancy
GROUP BY
    i.OBJECT_ID,
    i.index_id,
    i.name
ORDER BY
    OBJECT_NAME(i.OBJECT_ID),
    i.index_id