[MSSQL]如何取得某資料庫裡的table空間使用狀況

有關資料庫的討論, 都可以在這發表哦~~
回覆文章
頭像
tim
文章: 1379
註冊時間: 2008年 11月 26日, 00:49

[MSSQL]如何取得某資料庫裡的table空間使用狀況

文章 tim »

利用 sp_MSforeachtable 配合操作, 指令如下,

代碼: 選擇全部

create table #used_table
( name nvarchar(255),
 rows int,
 reserved nvarchar(255),
 data nvarchar(255),
 index_size nvarchar(255),
 unused nvarchar(255)
)

EXEC sp_MSforeachtable
@command1 = "insert into #used_table exec sp_spaceused '?'"

select * from #used_table
drop table #used_table 
由於帶出來的資料是文字, 不方便排序, 再加上一點功能, 帶出以 kb 為單位的使用空間欄位, 方便排序並列表,

代碼: 選擇全部

create table #used_table
( name nvarchar(255),
 rows int,
 reserved nvarchar(255),
 data nvarchar(255),
 index_size nvarchar(255),
 unused nvarchar(255)
)

EXEC sp_MSforeachtable
@command1 = "insert into #used_table exec sp_spaceused '?'"

select * from #used_table

create table #used_table2
( name nvarchar(255),
 rows int,
 reserved_KB int,
 data_KB int,
 index_size_KB int,
 unsed_KB int
)

insert into #used_table2 select name, rows, replace(reserved, ' KB', ''), replace(data, ' KB', ''), replace(index_size, ' KB', ''), replace(unused, ' KB', '') from #used_table
select * from #used_table2  

drop table #used_table
drop table #used_table2
多多留言, 整理文章, 把經驗累積下來.....
頭像
tim
文章: 1379
註冊時間: 2008年 11月 26日, 00:49

Re: [MSSQL]如何取得某資料庫裡的table空間使用狀況

文章 tim »

其中各欄位的說明如下:

name 資料表名稱
rows 資料表內的資料筆數
reseved_KB 該表的總大小
data_KB 該表的資料存放佔用大小
index_size_KB 該表的索引佔用大小
unused_KB 該表的未使用空間

所以 reserved_KB = data_KB + index_size_KB + unused_KB 三項的值
多多留言, 整理文章, 把經驗累積下來.....
頭像
tim
文章: 1379
註冊時間: 2008年 11月 26日, 00:49

Re: [MSSQL]如何取得某資料庫裡的table空間使用狀況

文章 tim »

如何找出SQL Server 「全部」資料庫大於 2 GB 的Table
https://ithelp.ithome.com.tw/questions/10206967

Get tables size info from all databases from SQL Server
https://stackoverflow.com/questions/345 ... sql-server

代碼: 選擇全部

IF OBJECT_ID('tempdb.dbo.#space') IS NOT NULL
    DROP TABLE #space

CREATE TABLE #space (
      [db_name] SYSNAME
    , obj_name SYSNAME
    , total_pages BIGINT
    , used_pages BIGINT
    , total_rows BIGINT
)

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = STUFF((
    SELECT '
    USE [' + d.name + ']
    INSERT INTO #space ([db_name], obj_name, total_pages, used_pages, total_rows)
    SELECT DB_NAME(), SCHEMA_NAME(o.[schema_id]) + ''.'' + o.name, t.total_pages, t.used_pages, t.total_rows
    FROM (
        SELECT
              i.[object_id]
            , total_pages = SUM(a.total_pages)
            , used_pages = SUM(a.used_pages)
            , total_rows = SUM(CASE WHEN i.index_id IN (0, 1) AND a.[type] = 1 THEN p.[rows] END)
        FROM sys.indexes i
        JOIN sys.partitions p ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id
        JOIN sys.allocation_units a ON p.[partition_id] = a.container_id
        WHERE i.is_disabled = 0
            AND i.is_hypothetical = 0
        GROUP BY i.[object_id]
    ) t
    JOIN sys.objects o ON t.[object_id] = o.[object_id]
    WHERE o.name NOT LIKE ''dt%''
        AND o.is_ms_shipped = 0
        AND o.type = ''U''
        AND o.[object_id] > 255;'
    FROM sys.databases d
    WHERE d.[state] = 0
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')

EXEC sys.sp_executesql @SQL

SELECT 
      [db_name]
    , obj_name
    , total_rows
    , total_space = CAST(total_pages * 8. / 1024 AS DECIMAL(18,2))
    , used_space = CAST(used_pages * 8. / 1024 AS DECIMAL(18,2))
    , unused_space = CAST((total_pages - used_pages) * 8. / 1024 AS DECIMAL(18,2))
FROM #space
多多留言, 整理文章, 把經驗累積下來.....
回覆文章