查看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删除。