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

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

2013-11-15 14:50:12  來源: SQL Server 

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

 

  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/SQLServer/201311/22406.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.