一条语句返回一个数据库中所有表的空间使用情况

 

USE

 

 

< db_name

>

sp_MSforeachtable

 

 

@command1 = 'EXEC sp_spaceused ''?''' , @whereand =

'or OBJECTPROPERTY(o.id, N''IsSystemTable'') = 1'

-----改进版

IF

 

EXISTS (SELECT * FROM sysobjects WHERE name = N'TabSize' AND type in (N'U'))

DROP

 

TABLE dbo.TabSize

CREATE

 

TABLE dbo.TabSize(

Name

 

CHAR (255),

Rows

INT,

Reserved

CHAR (255),

Data

CHAR (255),

Index_size

CHAR (255),

Unused

CHAR (255)

)

 

exec

 

sp_MSForEachTable

'INSERT INTO dbo.TabSize EXEC sp_spaceused "?"'

SELECT

 

*

FROM

 

dbo.TabSize

ORDER

 

BY convert(int,replace(Data,' KB','')) DESC

Drop

 

table dbo.TabSize