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]gocreate table TableSpace    (      TableName varchar(20) ,      RowsCount char(11) ,      Reserved varchar(18) ,      Data varchar(18) ,      Index_size varchar(18) ,      Unused varchar(18)    )godeclare @sql varchar(500)declare @TableName varchar(20)declare mCursor cursorforselect name from sysobjects where xtype='U'open mCursorfetch NEXT from mCursor into @TableNamewhile @@fetch_status = 0     begin        set @sql = 'insert into TableSpace '        set @sql = @sql + ' exec sp_spaceused ''' + @TableName + ''' '        exec (@sql)        fetch NEXT from mCursor into @TableName    endclose mCursordeallocate mCursorgo--顯示結果select TableName,RowsCount from TableSpace--方法二select  b.name as tablename ,        c.row_count as datacountfrom    sys.indexes a ,        sys.objects b ,        sys.dm_db_partition_stats cwhere   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 datacountfrom    sysindexes a ,        sysobjects bwhere   a.id = b.id        and a.indid < 2        and objectproperty(b.id, 'IsMSShipped') = 0         --建議使用後兩種方式，對於SQL SERVER 2005來說，三種方法都好使，如果是其他板本，可以逐一測試一下。