跳到主要內容

找出SQLServe某個Database裏每個table的使用量

印象中在下用的某個架站機有Log功能，剛才發現DB成長到500MB，但是忘記是哪個table在Log，於是找到Script to analyze table space usage，找出此資料庫中列數最多或使用磁碟容量最多的table，就可以解決。

Update: 強者 evi1[c] 在回應中提供了他的SQL，語法精簡效率超高，我已改用他的版本，特此誌謝。

為避免原站無法連結，備份如下：（版權歸原作者Michael Valentine Jones所有）
-- Script to analyze table space usage using the
-- output from the sp_spaceused stored procedure
-- Works with SQL 7.0, 2000, and 2005

set nocount on

print 'Show Size, Space Used, Unused Space, Type, and Name of all database files'

select
 [FileSizeMB] =
  convert(numeric(10,2),sum(round(a.size/128.,2))),
        [UsedSpaceMB] =
  convert(numeric(10,2),sum(round(fileproperty( a.name,'SpaceUsed')/128.,2))) ,
        [UnusedSpaceMB] =
  convert(numeric(10,2),sum(round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2))) ,
 [Type] =
  case when a.groupid is null then '' when a.groupid = 0 then 'Log' else 'Data' end,
 [DBFileName] = isnull(a.name,'*** Total for all files ***')
from
 sysfiles a
group by
 groupid,
 a.name
 with rollup
having
 a.groupid is null or
 a.name is not null
order by
 case when a.groupid is null then 99 when a.groupid = 0 then 0 else 1 end,
 a.groupid,
 case when a.name is null then 99 else 0 end,
 a.name

create table #TABLE_SPACE_WORK
(
 TABLE_NAME  sysname  not null ,
 TABLE_ROWS  numeric(18,0) not null ,
 RESERVED  varchar(50)  not null ,
 DATA   varchar(50)  not null ,
 INDEX_SIZE  varchar(50)  not null ,
 UNUSED   varchar(50)  not null ,
)

create table #TABLE_SPACE_USED
(
 Seq  int  not null 
 identity(1,1) primary key clustered,
 TABLE_NAME  sysname  not null ,
 TABLE_ROWS  numeric(18,0) not null ,
 RESERVED  varchar(50)  not null ,
 DATA   varchar(50)  not null ,
 INDEX_SIZE  varchar(50)  not null ,
 UNUSED   varchar(50)  not null ,
)

create table #TABLE_SPACE
(
 Seq  int  not null
 identity(1,1) primary key clustered,
 TABLE_NAME  SYSNAME  not null ,
 TABLE_ROWS  int   not null ,
 RESERVED  int   not null ,
 DATA   int   not null ,
 INDEX_SIZE  int   not null ,
 UNUSED   int   not null ,
 USED_MB    numeric(18,4) not null,
 USED_GB    numeric(18,4) not null,
 AVERAGE_BYTES_PER_ROW  numeric(18,5) null,
 AVERAGE_DATA_BYTES_PER_ROW numeric(18,5) null,
 AVERAGE_INDEX_BYTES_PER_ROW numeric(18,5) null,
 AVERAGE_UNUSED_BYTES_PER_ROW numeric(18,5) null,
)

declare @fetch_status int
declare @proc  varchar(200)
select @proc = rtrim(db_name())+'.dbo.sp_spaceused'

declare Cur_Cursor cursor local
for
select
 TABLE_NAME = 
 rtrim(TABLE_SCHEMA)+'.'+rtrim(TABLE_NAME)
from
 INFORMATION_SCHEMA.TABLES 
where
 TABLE_TYPE = 'BASE TABLE'
order by
 1

open Cur_Cursor

declare @TABLE_NAME  varchar(200)

select @fetch_status = 0
while @fetch_status = 0
 begin
 fetch next from Cur_Cursor
 into
  @TABLE_NAME

 select @fetch_status = @@fetch_status

 if @fetch_status <> 0
  begin
  continue
  end

 truncate table #TABLE_SPACE_WORK

 insert into #TABLE_SPACE_WORK
  (
  TABLE_NAME,
  TABLE_ROWS,
  RESERVED,
  DATA,
  INDEX_SIZE,
  UNUSED
  )
 exec @proc @objname = 
  @TABLE_NAME ,@updateusage = 'true'


 -- Needed to work with SQL 7
 update #TABLE_SPACE_WORK
 set
  TABLE_NAME = @TABLE_NAME

 insert into #TABLE_SPACE_USED
  (
  TABLE_NAME,
  TABLE_ROWS,
  RESERVED,
  DATA,
  INDEX_SIZE,
  UNUSED
  )
 select
  TABLE_NAME,
  TABLE_ROWS,
  RESERVED,
  DATA,
  INDEX_SIZE,
  UNUSED
 from
  #TABLE_SPACE_WORK

 end  --While end

close Cur_Cursor

deallocate Cur_Cursor

insert into #TABLE_SPACE
 (
 TABLE_NAME,
 TABLE_ROWS,
 RESERVED,
 DATA,
 INDEX_SIZE,
 UNUSED,
 USED_MB,
 USED_GB,
 AVERAGE_BYTES_PER_ROW,
 AVERAGE_DATA_BYTES_PER_ROW,
 AVERAGE_INDEX_BYTES_PER_ROW,
 AVERAGE_UNUSED_BYTES_PER_ROW

 )
select
 TABLE_NAME,
 TABLE_ROWS,
 RESERVED,
 DATA,
 INDEX_SIZE,
 UNUSED,
 USED_MB   =
  round(convert(numeric(25,10),RESERVED)/
  convert(numeric(25,10),1024),4),
 USED_GB   =
  round(convert(numeric(25,10),RESERVED)/
  convert(numeric(25,10),1024*1024),4),
 AVERAGE_BYTES_PER_ROW =
  case
  when TABLE_ROWS <> 0
  then round(
  (1024.000000*convert(numeric(25,10),RESERVED))/
  convert(numeric(25,10),TABLE_ROWS),5)
  else null
  end,
 AVERAGE_DATA_BYTES_PER_ROW =
  case
  when TABLE_ROWS <> 0
  then round(
  (1024.000000*convert(numeric(25,10),DATA))/
  convert(numeric(25,10),TABLE_ROWS),5)
  else null
  end,
 AVERAGE_INDEX_BYTES_PER_ROW =
  case
  when TABLE_ROWS <> 0
  then round(
  (1024.000000*convert(numeric(25,10),INDEX_SIZE))/
  convert(numeric(25,10),TABLE_ROWS),5)
  else null
  end,
 AVERAGE_UNUSED_BYTES_PER_ROW =
  case
  when TABLE_ROWS <> 0
  then round(
  (1024.000000*convert(numeric(25,10),UNUSED))/
  convert(numeric(25,10),TABLE_ROWS),5)
  else null
  end
from
 (
 select
  TABLE_NAME,
  TABLE_ROWS,
  RESERVED = 
  convert(int,rtrim(replace(RESERVED,'KB',''))),
  DATA  = 
  convert(int,rtrim(replace(DATA,'KB',''))),
  INDEX_SIZE = 
  convert(int,rtrim(replace(INDEX_SIZE,'KB',''))),
  UNUSED  = 
  convert(int,rtrim(replace(UNUSED,'KB','')))
 from
  #TABLE_SPACE_USED aa
 ) a
order by
 TABLE_NAME

print 'Show results in descending order by size in MB'

select * from #TABLE_SPACE order by USED_MB desc
go

drop table #TABLE_SPACE_WORK
drop table #TABLE_SPACE_USED 
drop table #TABLE_SPACE


後記：美麗的MIS小姐又提供SQL資料庫容量檢查(How to check Database reference size?)
再備份如下：（版權歸遺忘技術的角落或其朋友所有）
DECLARE @tblname varchar(50)            --資料表名稱(使用者資料表名稱變數) 
if exists (select * from dbo.sysobjects 
            where id = object_id(N'dbo.#tmpStatics') 
              and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
DROP TABLE #tmpStatics   --暫存資料表

CREATE TABLE #tmpStatics (        --建立暫存資料表 
  name       varchar(50)  NULL ,  --資料表名稱 
  rows       varchar(50)  NULL ,  --資料表現有的資料列數 
  reserved   varchar(50)  NULL ,  --資料庫中的物件所配置的空間大小 
  data       varchar(50)  NULL ,  --資料所用的空間大小 
  index_size varchar(50)  NULL ,  --索引所用的空間大小 
  unused     varchar(50)  NULL )  --保留給資料庫中之物件但尚未使用的空間大小 
DECLARE cur_TableStatics CURSOR FORWARD_ONLY FOR 
    SELECT name FROM sysobjects 
     WHERE xtype='U' ORDER BY name               --取得使用者資料表名稱 
OPEN cur_TableStatics 
FETCH NEXT FROM cur_TableStatics 
INTO @tblname 
  WHILE @@FETCH_STATUS = 0 
  BEGIN  
    INSERT #tmpStatics 
      EXEC sp_spaceused @tblname,@updateusage = N'TRUE'    --顯示資料表的相關磁碟空間資訊 
    FETCH NEXT FROM cur_TableStatics 
    INTO @tblname 
  END 
CLOSE cur_TableStatics 
DEALLOCATE cur_TableStatics

SELECT name,rows, 
       CONVERT(NUMERIC(18,2),SUBSTRING(reserved,1,LEN(reserved)-2))/1024 AS Reserved, -- MB 
       CONVERT(NUMERIC(18,2),SUBSTRING(data,1,LEN(data)-2))/1024 AS Data, 
       CONVERT(NUMERIC(18,2),SUBSTRING(index_size,1,LEN(index_size)-2))/1024 AS Index_Siz, 
       CONVERT(NUMERIC(18,2),SUBSTRING(unused,1,LEN(unused)-2))/1024 AS Unused 
  FROM #tmpStatics 
WHERE rows <> '0' 
ORDER BY Data 
DROP TABLE #tmpStatics
標籤：

留言

evi1[c]寫道…
找所有 table 筆數的方式有個很快的方法, 我是拿來對付一個月會生出兩三千萬筆資料的 table。參考一下:

SELECT sysobjects.[name], max(sysindexes.[rows]) AS TableRows,
CAST(
CASE max(sysindexes.[rows])
WHEN 0 THEN -0
ELSE LOG10(max(sysindexes.[rows]))
END
AS NUMERIC(5,2))
AS L10_TableRows
FROM sysindexes INNER JOIN sysobjects ON sysindexes.[id] = sysobjects.[id]
WHERE sysobjects.xtype = 'U'
GROUP BY sysobjects.[name]
ORDER BY max(rows) DESC
GO
11/26/2009 7:55 下午
鳥毅寫道…
非常感謝 evi1[c] ，您的版本快速精簡，實在是太優秀了。
11/27/2009 8:10 上午
張貼留言

這個網誌中的熱門文章

自然人憑證讀卡機驅動程式

鳥毅用的是第一代的自然人憑證讀卡機，EZ100PU（後來有同事買EZmini可以讀SIM卡似乎更好），每年報稅時用一次。 本來只是要申請些政府業務，一時之間找不到光碟，沒想到在 驅動程式下載 居然看到Linux和Mac的驅動程式，剩下的就是政府單位的網頁和程式應該改版了吧！！！
2 則留言
閱讀完整內容

DBeaver 介面語言

DBeaver是我個人頗常用的一套跨平台Database管理工具，最近升級後發現Windows版本居然變成簡體中文，而且無法切換為英文。
張貼留言
閱讀完整內容

如何將較高版本SQL Server複製到低版本SQL Server (降級為舊版)並保留權限及資料庫圖表

一般若是要將SQL Server裡的Database轉往其他Server時，最簡單的方式就是備份(Backup)後再還原(Restore)，或者是䣃離(detach)後附加(attach)。 但是很不幸地，若是由較低版本(e.g. 2008)到較高版本(e.g. 2012)要怎麼辦呢？
張貼留言
閱讀完整內容