--计算一个数据库中所有表的大小
declare @id            NVARCHAR(100)

create table #spt_space

(

    [name]            NVARCHAR(50) null,

    [rows]          int null,

    [reserved]       NVARCHAR(50) null,

    [data]         NVARCHAR(50) null,

    [index_size]        NVARCHAR(50) null,

    [unused]         NVARCHAR(50) null

)

set nocount on

declare c_tables cursor for

select name from sysobjects where xtype = 'U'

open c_tables fetch next from c_tables into @id

while @@fetch_status = 0

begin

    /* Code from sp_spaceused */

    insert into #spt_space ([name],[rows],reserved,data,index_size,unused)

    EXEC sp_spaceused @id

    fetch next from c_tables   into @id

end

SELECT * FROM(

SELECT *,CAST(REPLACE(reserved,'KB','') AS float(3))/(1024*1024) AS reservedindex FROM #spt_space

) c

order by c.reservedindex desc

drop table #spt_space

close c_tables

deallocate c_tables

declare @lengh int;

declare @i int;

declare @tableName varchar(20);

DECLARE @SQLString NVARCHAR(500)

declare   @a   table   (a   int,b   varchar(100)) ;

set @tableName='tblDetail';

create table #spt_space

(

    [name]            NVARCHAR(50) null,

    [rows]          int null,

    [reserved]       NVARCHAR(50) null,

    [data]         NVARCHAR(50) null,

    [index_size]        NVARCHAR(50) null,

    [unused]         NVARCHAR(50) null

)

insert into #spt_space ([name],[rows],reserved,data,index_size,unused)

EXEC sp_spaceused @tableName;

declare @record_size int;

set @record_size = (select sum(length) from syscolumns  where object_name(id)=@tableName);

select cast(rows as numeric(16))*@record_size /(1024*1024*1024) ,@record_size as record_size ,rows from   #spt_space 

drop table #spt_space;