查看Sql表所占空间的大小

create table #tablespaceinfo 

(nameinfo varchar(50) ,

rowsinfo int ,

 reserved varchar(20) ,

datainfo

varchar(20) ,

index_size

varchar(20) ,

unused

varchar(20) )

declare

@tablename varchar(255)

declare

@cmdsql varchar(500)

DECLARE

Info_cursor CURSOR

FOR select o.name from dbo.sysobjects o where OBJECTPROPERTY(o.id, N'IsTable') = 1  

and o.name not like N'#%%' order by o.name

OPEN

FETCH NEXT FROM Info_cursor INTO @tablename WHILE @@FETCH_STATUS = 0

Info_cursor

 

BEGIN

set @tablename = 'SchemaName.'+@tablename 

if exists (select * from dbo.sysobjects where id = object_id(@tablename) and OBJECTPROPERTY(id, N'IsUserTable') = 1)

 

execute

sp_executesql

N'insert into #tablespaceinfo exec sp_spaceused @tbname',

N'@tbname varchar(255)',

@tbname

 

= @tablename

FETCH NEXT FROM Info_cursor INTO @tablename 

 

 

END

CLOSE

 

Info_cursor

DEALLOCATE

 

Info_cursor

GO

 

 

sp_spaceused 

@updateusage = 'TRUE'

select 

 

* from#tablespaceinfo

order by cast(left(ltrim(rtrim(reserved)) , len(ltrim(rtrim(reserved)))-2) as int) desc

 

 

使用时注意替换SchemaName，若为 Sql2000，则将

set @tablename = 'SchemaName.'+@tablename+@tablename+@tablename 删除。