--获取一个数据库中所有表(每个表)的记录数 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 a.name,b.rows from sysobjects a,sysindexes b where a.xtype='U' and a.Id=b.Id and b.Indid<2 order by a.name
select a.name,b.rowcnt from sysobjects a,sysindexes b where a.xtype='U' and a.Id=b.Id and b.Indid<2 order by a.name
大比拼,哈哈,1楼的是错的 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' set @sql = @sql +' join ( select i.rows,o.name from sysindexes i,sysobjects o where o.xtype=''U'' and o.Id=i.Id and i.Indid<2 ) b ' set @sql = @sql + ' on a.name = b.name and a.num <> b.rows ' exec(@sql) /* name num rows name --------------------- ----------- ----------- -------------------------------------------------------------------------------------------------------------------------------- T_ResearchUser 244 249 T_ResearchUser T_T 1 0 T_T */
EXEC sp_MSforeachtable @command1="print '?'", @command2= "SELECT count(*) FROM ? "
EXEC sp_MSforeachtable @command1="print '?'", @command2="sp_spaceused '?'", @command3= "SELECT count(*) AS ColCount FROM ? "
SELECT a.name, b.rows FROM sysobjects a WITH(NOLOCK), sysindexes b WITH(NOLOCK) WHERE a.xtype = 'U ' AND b.indid IN (0, 1) AND a.id = b.id 更过无数次讨论,这行书不准
declare @sql varchar(8000) select @sql=isnull(@sql+' union all ','')+'select count(*) as [con] from ['+name+']' from (select name from sysobjects where xtype='U')tp exec( 'select sum(con) from ('+@sql+')tp')8332 简单点的,楼上是我花100分买的 呵呵
请高手解释一下 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 行) */
sp_msforeachtable 'select ''?'' 表名, count(*) 记录数 FROM ?'
select sysobjects.name,sysindexes.rows from sysobjects ,sysindexes where sysobjects.xtype='U' and sysobjects.Id=sysindexes.Id and sysindexes.Indid<2 order by sysobjects.name
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) ---------------------------------------------------------------------------- 当len(@sql)>4000 时会自动截去后面的。 所有在我的数据库中执行报错。
不行就变通变通,varchar(max)
搞定 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
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)(255 行受影响)
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(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)(30 行受影响)佩服楼上
EXEC sp_MSforeachtable @command1= "SELECT '?' as name, count(*) AS num FROM ? "
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 a.name,b.rows
from sysobjects a,sysindexes b
where a.xtype='U'
and a.Id=b.Id
and b.Indid<2
order by a.name
select a.name,b.rowcnt
from sysobjects a,sysindexes b
where a.xtype='U'
and a.Id=b.Id
and b.Indid<2
order by a.name
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'
set @sql = @sql +' join ( select i.rows,o.name from sysindexes i,sysobjects o where o.xtype=''U'' and o.Id=i.Id and i.Indid<2 ) b '
set @sql = @sql + ' on a.name = b.name and a.num <> b.rows '
exec(@sql)
/*
name num rows name
--------------------- ----------- ----------- --------------------------------------------------------------------------------------------------------------------------------
T_ResearchUser 244 249 T_ResearchUser
T_T 1 0 T_T
*/
EXEC sp_MSforeachtable @command1="print '?'",
@command2= "SELECT count(*) FROM ? "
EXEC sp_MSforeachtable @command1="print '?'",
@command2="sp_spaceused '?'",
@command3= "SELECT count(*) AS ColCount FROM ? "
sysindexes b WITH(NOLOCK)
WHERE a.xtype = 'U ' AND b.indid IN (0, 1) AND a.id = b.id 更过无数次讨论,这行书不准
在MS SQL 数据库中每个表都在sysindexes 系统表中拥有至少一条记录,
该记录中的rows 字段会定时记录表的记录总数。
下面是sysindexes 表的相关记录的含义:列名 数据类型 描述
id int 表ID(如果 indid = 0 或255)。
否则为索引所属表的IDIndid smallint
索引ID: 0=表
1=聚簇索引 >1=非聚簇索引 255=具有text或image数据的表条目。
rows int
基于indid=0 和 indid=1地数据级行数,
该值对于indid>1重 复。
如果indid=255,rows设置为0。
当表没有聚簇索引时,Indid = 0 否则为 1。
select @sql=isnull(@sql+' union all ','')+'select count(*) as [con] from ['+name+']'
from (select name from sysobjects where xtype='U')tp
exec( 'select sum(con) from ('+@sql+')tp')8332
简单点的,楼上是我花100分买的 呵呵
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 行)
*/
--在MS SQL 数据库中每个表都在sysindexes 系统表中拥有至少一条记录,
--该记录中的rows 字段会定时记录表的记录总数。
from sysobjects ,sysindexes
where sysobjects.xtype='U'
and sysobjects.Id=sysindexes.Id
and sysindexes.Indid<2
order by sysobjects.name
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)
----------------------------------------------------------------------------
当len(@sql)>4000 时会自动截去后面的。
所有在我的数据库中执行报错。
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
---------------------
你的结果集中有255行吗?
当表的个数超过30个时,拼接的@sql 的长度有可能超过4000,
这时我的SQL SERVER 就强行截断@sql,后面的不要了,导致执行动态
语句错误,不知你们的怎么可以。有时正好截断的不是两个单引号之间的,
语句可以执行,但结果的行数少了很多。
ls采用临时表方式,就没有这些限制了。
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)(255 行受影响)
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
我的是SQL 2000
你的代码我运行不了。
第 1 行: 'max' 附近有语法错误。
服务器: 消息 137,级别 15,状态 1,行 2
必须声明变量 '@sql'。
服务器: 消息 170,级别 15,状态 1,行 3
第 3 行: 'tp' 附近有语法错误。
服务器: 消息 137,级别 15,状态 1,行 4
必须声明变量 '@sql'。
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)(30 行受影响)佩服楼上