none
如何列出特定資料庫的每一個Table目前的使用大小?? RRS feed

  • 問題

  • 各位前輩好

         小弟是一個系統工程師(比較偏系統應用,對SQL幾乎不懂),公司使用的一套軟體使用SQL Server去當作他的資料存放區。

    最近發現資料庫使用過大,想看看是哪一個Table造成的,但是因為該軟體建立的資料庫裡面有超過200以上的Table...

    請問諸位DBA 先進,小弟想要列出Table Name跟目前使用的磁碟空間有多少....這有辦法使用Script列出來嗎??

    以及如果有列出來結果,可以匯出結果成一個類似html/csv 的方式方便儲存嗎??

    感謝各位

    wyld


    wyldkao

    2012年4月11日 上午 09:09

解答

所有回覆

  • 你可以參考這篇的做法,直接拿他寫好的SQL來用。

    SELECT t.schema_name+ ' – '+ t.table_name as schema_table
    , t.index_name
    , sum(t.used) as used_in_kb
    , sum(t.reserved) as reserved_in_kb
    , sum(t.tbl_rows) as rows
    from
    (
    SELECT s.Name schema_name
    , o.Name table_name
    , coalesce(i.Name, 'HEAP') index_name
    , p.used_page_count * 8 used
    , p.reserved_page_count * 8 reserved
    , p.row_count ind_rows
    , case when i.index_id in ( 0, 1 ) then p.row_count else 0 end tbl_rows
    FROM sys.dm_db_partition_stats p
    INNER JOIN sys.objects as o
    ON o.object_id = p.object_id
    INNER JOIN sys.schemas as s
    ON s.schema_id = o.schema_id
    LEFT OUTER JOIN sys.indexes as i
    on i.object_id = p.object_id and i.index_id = p.index_id
    WHERE o.type_desc = 'USER_TABLE'
    and o.is_ms_shipped = 0
    ) as t
    GROUP BY
    t.schema_name, t.table_name
    , t.index_name
    ORDER BY
    5 desc

    另外你可以利用SSMS提供的功能,設定RESULT TO FILE把結果存到檔案,不過他的格式rpt。


    以上說明若有錯誤請指教,謝謝。
    http://www.dotblogs.com.tw/terrychuang/


    2012年4月11日 上午 09:53
    版主
  • hi

    開啟 SSMS 輸入下面 scripts 嘗試看看

    use ricotest1
    EXEC sp_MSforeachtable 'EXEC sp_spaceused ''?'''

    參考

    http://www.dotblogs.com.tw/ricochen/archive/2012/01/19/66713.aspx


    保證解答-微軟技術支援服務

    • 已標示為解答 wyldkao 2012年4月16日 上午 02:58
    2012年4月12日 上午 01:02
    版主
  • HI ricoisme

          我測試過您熱心提供的指令,可以順利列出,只是有一個小問題,就是它會出現"查詢已超過結果方格中可以顯示的結果集最大數目,方格中只會顯示前100個結果集",那我要怎樣看到所以的資訊....比如說我要怎樣匯出用哪一個工具來檢視執行出來的所有資訊(抱歉,我真的對SQL不懂,所以只能這樣問)

         另外您blogs第一個指令,我執行 (改為我的DB名稱,其餘不改)後出現"訊息102,層級15,狀態1,行1 接近''.'之處的語法不正確,這是因為我用SQL2005 SP3的關係嗎 ??

    1.列出資料庫中所有資料表筆數

    use ricotest1

    EXEC sp_MSforeachtable 'select ''?'' as 資料表名稱, Count(*) as 資料總筆數 from ?'


    wyldkao

    2012年4月12日 上午 02:26
  • 您好,請問這需要改任何東西嗎?

    我目前只知道DB name...其餘Table因為太多無法手動填入..且我就是想要直接列出...這樣這指令可以做到嗎??(抱歉,因為我不懂SQL,所以我也不知道這段SQL Command我需要改哪邊...)

    wyld


    wyldkao

    2012年4月12日 上午 02:28
  • HI ricoisme

          我測試過您熱心提供的指令,可以順利列出,只是有一個小問題,就是它會出現"查詢已超過結果方格中可以顯示的結果集最大數目,方格中只會顯示前100個結果集",那我要怎樣看到所以的資訊....比如說我要怎樣匯出用哪一個工具來檢視執行出來的所有資訊(抱歉,我真的對SQL不懂,所以只能這樣問)

         另外您blogs第一個指令,我執行 (改為我的DB名稱,其餘不改)後出現"訊息102,層級15,狀態1,行1 接近''.'之處的語法不正確,這是因為我用SQL2005 SP3的關係嗎 ??

    1.列出資料庫中所有資料表筆數

    use ricotest1

    EXEC sp_MSforeachtable 'select ''?'' as 資料表名稱, Count(*) as 資料總筆數 from ?'


    wyldkao

    接近''.'之處的語法不正確

    你確認你語法沒有輸入錯誤??

    你輸入的 DB NAME 是???

    這和SQL2005 SP3沒有任何關係

    use ricotest1
    EXEC sp_MSforeachtable 'select ''?'' as 資料表名稱, Count(*) as 資料總筆數 from ?'


    保證解答-微軟技術支援服務

    • 已提議為解答 Felaray 2017年7月25日 上午 11:14
    2012年4月12日 上午 05:59
    版主
  • 您好,請問這需要改任何東西嗎?

    我目前只知道DB name...其餘Table因為太多無法手動填入..且我就是想要直接列出...這樣這指令可以做到嗎??(抱歉,因為我不懂SQL,所以我也不知道這段SQL Command我需要改哪邊...)

    wyld


    wyldkao



    保證解答-微軟技術支援服務

    2012年4月12日 上午 06:03
    版主
  • 感謝您的熱心回應

    以下為我執行後的Screenshot


    wyldkao

    2012年4月12日 上午 06:39
  • 感謝您的熱心回應

    以下為我執行後的Screenshot


    wyldkao

    DB Name 用 [ ]包起來

    use [ricotest1]


    保證解答-微軟技術支援服務

    2012年4月12日 上午 09:20
    版主
  • 很感謝你的回應

    只是似乎還是一樣的結果...


    wyldkao

    2012年4月12日 上午 09:51
  • 你的SQL SERVER是什麼版本?

    建議你用TerryChuang 的作法

    若資料量大select count(*) 會影響DB效能

    2012年4月12日 上午 10:14
  • 很感謝你的回應

    只是似乎還是一樣的結果...


    wyldkao

    我大概知道你的問題了,執行以下語法查看你的table name

    select name,type_desc
    from sys.tables
    where name like '%.%'

    這是資料表名稱造成,和版本沒有任何關係


    保證解答-微軟技術支援服務

    2012年4月13日 上午 01:40
    版主
  • 很感謝你的回應

    只是似乎還是一樣的結果...


    wyldkao

    可以透過下面 scripts 取得資料表筆數和資料表大小 

    use ricotest1
    EXEC sp_MSforeachtable 'EXEC sp_spaceused ''?'''


    保證解答-微軟技術支援服務


    2012年4月13日 上午 01:56
    版主
  • 大概是我沒說清楚問版本的用意

    一是怕TerryChuang 的語法沒辦法跑

    二是想建議使用Management Studio 報表->[排名最前面資料表的磁碟使用量]
    裡面有wyldkao要的匯出儲存功能

    2012年4月13日 上午 04:20
  • 它會出現"查詢已超過結果方格中可以顯示的結果集最大數目,方格中只會顯示前100個結果集",那我要怎樣看到所以的資訊....
    1. 在查詢結果的視窗中,按下 Ctrl + T,切換成「以文字顯示結果」
    2. 再執行一次指令,就不會出現上述的訊息
    我要怎樣匯出用哪一個工具來檢視執行出來的所有資訊
    1. 「結果」的視窗中,按下滑鼠右鍵,選擇「儲存結果」
    2. 「儲存結果」對話視窗中,選擇適當的資料夾,輸入檔案名稱,按下「存檔」按鈕,即可將結果存檔成純文字檔(雖然附檔名是 .rpt)


    如果要將每個資料表目前使用的空間狀態匯出成 html/csv,可以參考下面的步驟進行:

    1. 安裝教學:SQL Server Powershell Extensions (SQLPSX) v2.3.2.1
    2. 參考這 2 篇文章:
      1. 使用 Windows PowerShell 將資料匯出成 .html 檔案(含安裝 SQLPSX 模組教學)
      2. 使用 Windows PowerShell 將 SQL Server 資料匯出成 .csv 檔案

      以下面的程式碼為例,會從電腦名稱為 alex-sql 的 SQL Server 中,資料庫名稱為 AdventureWorks 的每一個資料表使用的空間狀態儲存到 d:\alex.html
      Get-SqlData "alex-sql" AdventureWorks `
      "EXEC sp_MSforeachtable 'EXEC sp_spaceused ''?'''" | `
      ConvertTo-Html -Property name, rows, reserved, data, index_size, unused `
      -Title "資料表" > d:\alex.html


    ☞ 這裡是「免費的討論區」付費支援服務請造訪 此處享受尊榮服務
    如果回覆對您有幫助,請記得按下標示為解答」




    2012年4月15日 下午 05:33
    版主