sql server 获取数据库中每个表的磁盘占用情况

create   table  tmp (name  varchar ( 500 ),rows  int ,reserved  varchar ( 500 ),
  data  varchar ( 500 ),index_size  varchar ( 500 ),unused  varchar ( 500 ))
insert   into  tmp (name,rows,reserved,
  data,index_size,unused)  exec  sp_msforeachTable  @Command1 = "sp_spaceused  ' ? ' " -- sp_spaceused 't_vehicle'
select   *   from  tmp  order   by   [ rows ]   desc  
drop   table  tmp

或者使用我自己写的，不过只有行数，没有使用系统存储过程

 1 declare   @maxrowcount   int
 2 declare   @maxtablename   varchar ( 200 )
 3 declare   @sql   nvarchar ( 2000 )
 4 declare   @tempcount   int
 5 set   @sql   =   '' ;
 6 set   @maxtablename   =   '' ;
 7 create   table  # rowcount
 8 (counts  int ,tname  varchar ( 200 ))
 9 declare  Tnamecursor  cursor   for   select  name  from  sysobjects  where  xtype  =   ' U '   and   OBJECTPROPERTY (id, N ' IsUserTable ' )  =   1
10 declare   @name   varchar ( 200 );
11 open  Tnamecursor
12 FETCH   NEXT   FROM  Tnamecursor  INTO   @name
13 WHILE   @@FETCH_STATUS   =   0
14      BEGIN
15          set   @sql   =   ' select @tempcount = count(*) from  ' + @name   + '   insert into #rowcount values (@tempcount,@name) ' ;
16 print   @sql             
17 exec  sp_executesql  @sql ,N ' @tempcount int,@name varchar(200) ' , @tempcount , @name
18              -- print @name+'total count:'+Convert(varchar(200),@tempcount)
19              if ( @maxrowcount   < @tempcount )
20                  begin
21                  set   @maxrowcount   =   @tempcount ;
22                  set   @maxtablename   =   @name ;
23                  end
24          FETCH   NEXT   FROM  Tnamecursor  INTO   @name
25      end
26 CLOSE  Tnamecursor
27 DEALLOCATE  Tnamecursor
28
29 select   *   from  # rowcount   order   by  counts  desc
30
31 drop   table  # rowcount
32
33
34
35
36
37

转载于:https://www.cnblogs.com/silverlighter/archive/2007/09/27/908362.html