--方法一
if exists ( select *
from dbo.sysobjects
where id = object_id(N'[dbo].[TableSpace]')
and objectproperty(id, N'IsUserTable') = 1 )
drop table [dbo].[TableSpace]
go
create table TableSpace
(
TableName varchar(20) ,
RowsCount char(11) ,
Reserved varchar(18) ,
Data varchar(18) ,
Index_size varchar(18) ,
Unused varchar(18)
)
go
declare @sql varchar(500)
declare @TableName varchar(20)
declare mCursor cursor
for
select name from sysobjects where xtype='U'
open mCursor
fetch NEXT from mCursor into @TableName
while @@fetch_status = 0
begin
set @sql = 'insert into TableSpace '
set @sql = @sql + ' exec sp_spaceused ''' + @TableName + ''' '
exec (@sql)
fetch NEXT from mCursor into @TableName
end
close mCursor
deallocate mCursor
go
--显示结果
select TableName,RowsCount from TableSpace
--方法二
select b.name as tablename ,
c.row_count as datacount
from sys.indexes a ,
sys.objects b ,
sys.dm_db_partition_stats c
where a.[object_id] = b.[object_id]
AND b.[object_id] = c.[object_id]
AND a.index_id = c.index_id
AND a.index_id < 2
AND b.is_ms_shipped = 0
--方法三
select b.name as tablename ,
a.rowcnt as datacount
from sysindexes a ,
sysobjects b
where a.id = b.id
and a.indid < 2
and objectproperty(b.id, 'IsMSShipped') = 0
--建议使用后两种方式，对于SQL SERVER 2005来说，三种方法都好使，如果是其他板本，可以逐一测试一下。