22,209
社区成员
发帖
与我相关
我的任务
分享
--获取一个数据库中所有表(每个表)的记录数
declare @sql varchar(8000)
set @sql='select sum(num) as num from ('
select @sql=@sql+' select count(*) as num from '+name+' union all ' from sysobjects where xtype='u'
select @sql=@sql+' select 0) a'
exec(@sql)
declare @sql varchar(8000)
set @sql='select * from ('
select @sql=@sql+' select name = ''' + name + ''' , count(*) as num from '+name+' union all ' from sysobjects where xtype='u'
set @sql = left(@sql,len(@sql) - 10) + ')a'
exec(@sql)
select o.name,i.rows
from sysobjects o,sysindexes i
where o.xtype='U'
and o.Id=i.Id
and i.Indid<2
order by o.name
EXEC sp_MSforeachtable @command1= "SELECT '?' as name, count(*) AS num FROM ? "
declare @sql varchar(8000),@sql2 varchar(8000),@sql3 varchar(8000)
select @sql=isnull(@sql+' union all ','')+'select count(*) as [con] from ['+name+']'
from (select top 10 name from sysobjects where xtype='U')tp
select @sql2=isnull(@sql2+' union all ','')+'select count(*) as [con] from ['+name+']'
from (select top 10 name from sysobjects where xtype='U')tp
select @sql3=isnull(@sql3+' union all ','')+'select count(*) as [con] from ['+name+']'
from (select top 10 name from sysobjects where xtype='U')tp
exec(@sql+' union all '+@sql2+' union all '+@sql3)
declare @s varchar(8000),@i int,@j int
create table #1(s int,n varchar(50))
select identity(int,1,1) id,name n into # from sysobjects where xtype='U'
select @j=count(*) from #
set @i = 1
while @i <= @j
begin
select @s ='insert #1 select count(*),'''+n+''' from ['+n+']' from # where id = @i--[n]
exec(@s)
set @i = @i +1
end
select * from #1 order by n
drop table #,#1
declare @sql varchar(max)
select @sql=isnull(@sql+' union all ','')+'select count(*) as [con] from ['+name+']'
from (select top 255 name from sysobjects where xtype='U')tp
exec(@sql)
declare @s varchar(8000),@i int,@j int
create table #1(s int,n varchar(50))
select identity(int,1,1) id,name n into # from sysobjects where xtype='U'
select @j=count(*) from #
set @i = 1
while @i <= @j
begin
select @s ='insert #1 select count(*),'''+n+''' from '+n from # where id = @i
exec(@s)
set @i = @i +1
end
select * from #1 order by n
drop table #,#1
--在MS SQL 数据库中每个表都在sysindexes 系统表中拥有至少一条记录,
--该记录中的rows 字段会定时记录表的记录总数。
sp_msforeachtable 'select ''?'' 表名, count(*) 记录数 FROM ?'
SELECT b.rows ,a.name FROM sysobjects a WITH(NOLOCK),
sysindexes b WITH(NOLOCK)
WHERE a.xtype = 'U ' AND b.indid IN (0, 1)
AND a.id = b.id and a.name = 'T_ResearchUser'
select count(*) from T_ResearchUser
/*
rows name
----------- ---------------
249 T_ResearchUser
(所影响的行数为 1 行)
-----------
244
(所影响的行数为 1 行)
*/