请教如何计算mssql中每张表的行数

请教如何计算mssql中每张表的行数，我是这样想的
select count(*) from (select Name from sysobjects where xtype='U')结果不行

追问：没明白 。能给全嘛？谢谢

答案：需要动态执行sql：execute immediate（‘sql’）

其他：select count(*) from (select * from sysobjects where xtype='U')试试 代码有点多，应该是一个存储过程才能处理过来，
----------------------------------------------------------------
use master
go
EXEC sp_addmessage @msgnum = 55000, @severity = 16, 
   @msgtext = N'Objects of type %s do not have space allocated.', 
   @lang = 'us_english',@replace = 'replace'

EXEC sp_addmessage @msgnum = 55000, @severity = 16, 
   @msgtext = N'没有为类型 %1! 的对象分配的空间。', 
   @lang = '简体中文',@replace = 'replace'
go
if exists (select 1 from sysobjects where name = 'sp_spaceused2' and type = 'P')
 drop procedure sp_spaceused2
go
create procedure sp_spaceused2 (
 @type varchar(2) = 'U', -- The objects type we want size on.
 @updateusage varchar(5) = false -- Param. for specifying that
     -- usage info. should be updated.
)
as
create table #spt_space
(
 id  int not null primary key,
 name  sysname,
 rows  int null,
 reserved dec(15) null,
 data  dec(15) null,
 indexp  dec(15) null,
 unused  dec(15) null
)

/*
**  Check the object type.
*/
if @type not in ('U','S') -- no physical data storage.
  begin
   raiserror(55000,-1,-1,@type)
   return (1)
  end
/*
**  Check to see if user wants usages updated.
*/

if @updateusage is not null
 begin
  select @updateusage=lower(@updateusage)

  if @updateusage not in ('true','false')
   begin
    raiserror(15143,-1,-1,@updateusage)
    return(1)
   end
 end

if @updateusage = 'true'
 begin
  dbcc updateusage(0) with no_infomsgs
  print ' '
 end

set nocount on
--id, name, rows
insert into #spt_space (id, name, rows, data)
select i.id, o.name, i.rows, 0
from sysindexes i inner join sysobjects o on i.id = o.id 
where i.indid < 2 and o.type = @type

--reserved
update #spt_space
set reserved = r.reserved
from ( select o.id, sum(i.reserved) as reserved from sysindexes i inner join sysobjects o on i.id = o.id 
 where i.indid in (0, 1, 255) and o.type = @type group by o.id) r
where #spt_space.id = r.id

--data
update #spt_space
set data = data + r.pages
from ( select o.id, isnull(sum(i.dpages),0) as pages from sysindexes i inner join sysobjects o on i.id = o.id 
 where i.indid <2 and o.type = @type group by o.id) r
where #spt_space.id = r.id

update #spt_space
set data = data + r.used
from ( select o.id, isnull(sum(used), 0) as used from sysindexes i inner join sysobjects o on i.id = o.id 
 where i.indid = 255 and o.type = @type group by o.id) r
where #spt_space.id = r.id


--index page
update #spt_space
set indexp = r.used - data
from ( select o.id, sum(convert(dec(15),used)) as used from sysindexes i inner join sysobjects o on i.id = o.id 
 where i.indid in (0, 1, 255) and o.type = @type group by o.id) r
where #spt_space.id = r.id

--unused page
update #spt_space
set unused = reserved - r.used
from ( select o.id, sum(convert(dec(15),used)) as used from sysindexes i inner join sysobjects o on i.id = o.id 
 where i.indid in (0, 1, 255) and o.type = @type group by o.id) r
where #spt_space.id = r.id

--output
 select #spt_space.name,
  rows = convert(char(11), rows),
  reserved = ltrim(str(reserved * d.low / 1024.,15,0) +
    ' ' + 'KB'),
  data = ltrim(str(data * d.low / 1024.,15,0) +
    ' ' + 'KB'),
  index_size = ltrim(str(indexp * d.low / 1024.,15,0) +
    ' ' + 'KB'),
  unused = ltrim(str(unused * d.low / 1024.,15,0) +
    ' ' + 'KB')
 from #spt_space, master.dbo.spt_values d
  where d.number = 1
   and d.type = 'E' order by len(ltrim(rtrim(reserved))) desc,reserved desc

go
----------------------------------------------------------------
2、设置为系统存储过程
sp_MS_marksystemobject 'sp_spaceused2'
3、第三步，就可以查询了。
sp_spaceused2 

执行效果如下：
name       rows(这个就是每个表的行数)
spt_values	2508       	208 KB	96 KB	88 KB	24 KB
MSreplication_options	3          	16 KB	8 KB	8 KB	0 KB 
-----------------------------------------------------
以上测试在SQL2000以上版本中可以运行 

上一个：oracle数据库与mssql数据库的主要区别
下一个：安装MSSQL安装程序配置服务器失败,参考服务器错误日志和C：\WINDOWS\sqlstp.log了解更多