请问如何统计一个数据库中每有表的记录总数呢?


set nocount on
if object_id(N'tempdb.db.#temp') is not null
drop table #temp
create table #temp (name sysname,count numeric(18))

insert into #temp
select o.name,i.rows
from sysobjects o,sysindexes i
where o.id=i.id and o.Xtype='U' and i.indid<2

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

这是我在网上找的一个例子，但是好像不是很准确，
问一下能不能通过建立索引的方法来实现？

[此贴子已经被作者于2007-11-21 14:06:43编辑过]

IF EXISTS(SELECT * FROM dbo.SYSOBJECTS WHERE ID=OBJECT_ID(N'[GetAllTableRecordCount]')
AND OBJECTPROPERTY(ID,N'IsProcedure')=1)
DROP PROC [GetAllTableRecordCount]
GO

CREATE PROC dbo.[GetAllTableRecordCount]
AS
DECLARE @SQL NVARCHAR(4000),@TABLENAME NVARCHAR(128)
DECLARE @recordCount INT,@TCOUNT INT
DECLARE @T TABLE(tb_name NVARCHAR(128),RecordCount INT)

SET @recordCount=0
DECLARE db_Cur CURSOR FOR
SELECT NAME FROM dbo.SYSOBJECTS WHERE xtype=N'U'
OPEN db_Cur
FETCH NEXT FROM db_Cur INTO @TABLENAME
WHILE @@FETCH_STATUS=0
BEGIN
SET @SQL=N'SELECT @COUNT=COUNT(1) FROM ['+@TABLENAME+N']'
EXEC SP_EXECUTESQL @SQL,N'@COUNT INT OUTPUT',@TCOUNT OUTPUT
INSERT INTO @T VALUES(@TABLENAME,@TCOUNT)
SET @recordCount=@recordCount+@TCOUNT
FETCH NEXT FROM db_Cur INTO @TABLENAME
END
INSERT INTO @T VALUES('记录总数',@recordCount)
CLOSE db_Cur
DEALLOCATE db_Cur
SELECT * FROM @T
GO

EXEC GetAllTableRecordCount