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

#2

purana2007-11-21 14:13

1.通过sysobjects获得所有表名.
2.建一个Cursor查刚那些表.
3.声明一个变量来存放表里的记录数.

#3

purana2007-11-21 14:47

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

#4

kobe4122007-11-21 15:12

谢谢，，