SQL Server 查询数据库中所有表数据条数

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

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

and objectproperty(b.id, 'IsMSShipped') = 0

if exists (select * from dbo.sysobjects where id = object_id(n[dbo].[tablespace]) and objectproperty(id, nisusertable) = 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 cursor1 cursor
for
 select name from sysobjects where xtype=u

open cursor1
fetch next from cursor1 into @tablename

while @@fetch_status = 0
begin
 set @sql = insert into tablespace
 set @sql = @sql + exec sp_spaceused + @tablename +
 exec (@sql)
 fetch next from cursor1 into @tablename
end
close cursor1
deallocate cursor1
go

--显示结果
select * from tablespace
--order by tablename
--order by tablename asc   --按表名称，用于统计表
--order by rowscount desc   --按行数量，用于查看表行数
--order by reserved desc, data desc  --按占用空间
--order by index_size desc, reserved desc  --按索引空间查看
go

--查看库的使用状况,可以随时执行的。
--exec sp_spaceused
--go