SQL Server 得到数据库中所有表的名称及数据条数

提到单个表的数据条数，大家都会想到 select count(*) from tablename

如果是要得到数据库中所有表的条数呢？我们来看几种最常见的方式：

--方法一 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来说，三种方法都好使，如果是其他板本，可以逐一测试一下。