熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> MySQL >> 正文

獲取SQL Server數據庫裡表占用容量大小

2013-11-23 20:58:57  來源: MySQL 

  其實只要使用系統內置的存儲過程sp_spaceused就可以得到表的相關信息
  
  如sp_spaceused tablename
  
  以下是為了方便寫的一個存儲過程目的是把當前的所有表的相關信息全部都保存在一個指定的表裡面
  
  CREATE PROCEDURE get_tableinfo AS
  
  if not exists (select * from dbosysobjects where id = object_id(N[dbo][tablespaceinfo]) and OBJECTPROPERTY(id NIsUserTable) = )
  create table tablespaceinfo             創建結果存儲表
  (nameinfo varchar()
  rowsinfo int reserved varchar()
  datainfo varchar() 
  index_size varchar()
  unused varchar() )
  
  delete from tablespaceinfo 清空數據表
  
  declare @tablename varchar() 表名稱
  
  declare @cmdsql varchar()
  
  DECLARE Info_cursor CURSOR FOR
  select oname
  from dbosysobjects o where OBJECTPROPERTY(oid NIsTable) =
  and oname not like N#%% order by oname
  
  OPEN Info_cursor
  
  FETCH NEXT FROM Info_cursor
  INTO @tablename
  
  WHILE @@FETCH_STATUS =
  BEGIN
  
  if exists (select * from dbosysobjects where id = object_id(@tablename) and OBJECTPROPERTY(id NIsUserTable) = )
  execute sp_executesql
  Ninsert into tablespaceinfo exec sp_spaceused @tbname
  N@tbname varchar()
  @tbname = @tablename
  
  FETCH NEXT FROM Info_cursor
  INTO @tablename
  END
  
  CLOSE Info_cursor
  DEALLOCATE Info_cursor
  GO
  
  執行存儲過程
  
  exec get_tableinfo
  
  查詢運行該存儲過程後得到的結果
  
  select *
  from tablespaceinfo
  order by cast(left(ltrim(rtrim(reserved)) len(ltrim(rtrim(reserved)))) as int) desc
  

From:http://tw.wingwit.com/Article/program/MySQL/201311/29385.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.