统计数据库里所有表的记录数

create table #(id int identity ,tblname varchar(50),num int)

declare @name varchar(30)
declare roy cursor for select name from sysobjects
where xtype='U'

open roy

fetch next from roy into @name
while @@fetch_status=0
begin
declare @i int
declare @sql nvarchar(1000)
set @sql='select @n=count(1) from
exec sp_executesql @sql,N'@n int output',@i output
insert into # select @name,@I
fetch next from roy into @name
end
close roy
deallocate roy

select * from #

 

统计数据库里有多少表以及所有表的记录数

 

declare @sql varchar(8000),@count int,@step int
set nocount on
越大运行速度越快，但如果太大会造成生成的sql字符串超出限制导致语句不完整出错
set @step = 60 
if object_id(N'tempdb.db.#temp') is not null
  drop table #temp
create table #temp (name sysname,count numeric(18))
if object_id(N'tempdb.db.#temp1') is not null
  drop table #temp1
create table #temp1 (id int identity(1,1),name sysname)
insert into #temp1(name) select name from sysobjects where xtype = 'u';
set @count = @@rowcount
while @count>0
begin
  set @sql = ''
  select @sql = @sql + ' select ''' + name + ''',count(1) from ' + name + ' union'
  from #temp1
  where id > @count - @step and id <= @count
  print @sql
  set @sql = left(@sql,len(@sql) - 6)
  print @sql
  insert into #temp exec (@sql)
  set @count = @count - @step
end

select count(count) 总表数,sum(count) 总记录数 from #temp
select * from #temp
set nocount off