查找一个数据库内的所有表的行数

-- 这一部分是用来删除生成的表的

USE [YouDataBaseName]

IF EXISTS (

SELECT * FROM sys.objects WHERE object_id = OBJECT_ID ( 'dbo.TableRows' ) AND type in ( N'U' )

)

DROP TABLE [dbo]. TableRows

-- 这里是查询的部分

CREATE TABLE TableRows ( [name] nvarchar ( 255), rows bigint )

DECLARE @Name nvarchar ( 255), @Sql nvarchar ( 4000)

DECLARE TName CURSOR FOR

SELECT [name] FROM sysobjects WHERE xtype = 'U'

OPEN TName

FETCH NEXT FROM TName

INTO @Name

WHILE @@FETCH_STATUS = 0

BEGIN

SET @Sql= 'INSERT INTO TableRows ([name],rows) SELECT ''' + @name+ ''' AS name, COUNT(1) AS row FROM ' + @name

EXEC ( @Sql)

FETCH NEXT FROM TName INTO @name

END

CLOSE TName

DEALLOCATE TName

-- 这里是显示最后的结果

SELECT * FROM TableRows

SELECT sum ( rows ) FROM TableRows