IT狂想曲~

--// 查詢目前所有資料表的筆數、資料表使用空間及Index使用空間 //--
set statistics io  on
create table #dbtableinfo (
  [name]       nvarchar(70)   null,
  [rows]       int   null,
  [reserved]   nvarchar(30)   null,
  [data]       nvarchar(30)   null,
  [index_size] nvarchar(30)   null,
  [unused]     nvarchar(30)   null)
declare  @TableName varchar(50)
declare tablename cursor  for
select   [name]
from     sysobjects
where    xtype = 'U'
order by [name]
open tablename
fetch next from tablename
into @TableName
while @@fetch_status = 0
  begin
    print ' sp_spaceused '''
      + @TableName
      + ''''
    insert into #dbtableinfo
    exec sp_spaceused @TableName
    fetch next from tablename
    into @TableName
  end
close tablename
deallocate tablename
select   *
from     (select name,
                 convert(int,rows) rows,
                 convert(int,replace(reserved,' KB','')) [reserved(kb)],
                 convert(int,replace(data,' KB','')) [data(kb)],
                 convert(int,replace(index_size,' KB','')) [index_size(kb)],
                 convert(int,replace(unused,' KB','')) [unused(kb)]
          from   #dbtableinfo) a
order by [reserved(kb)] desc
drop table #dbtableinfo
set statistics io  off