|
提到单个表的数据条数,大家都会想到 select count(*) from tablename
如果是要得到数据库中所有表的条数呢?我们来看几种最常见的方式:
--方法一if exists ( select *from dbo.sysobjectswhere 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 beginset @sql = 'insert into TableSpace 'set @sql = @sql + ' exec sp_spaceused ''' + @TableName + ''' 'exec (@sql)fetch NEXT from mCursor into @TableNameendclose 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_idAND a.index_id < 2AND b.is_ms_shipped = 0 --方法三 select b.name as tablename ,a.rowcnt as datacountfrom sysindexes a ,sysobjects bwhere a.id = b.idand a.indid < 2and objectproperty(b.id, 'IsMSShipped') = 0 --建议使用后两种方式,对于SQL SERVER 2005来说,三种方法都好使,如果是其他板本,可以逐一测试一下。 |
|
|