随笔- 29  评论- 129  文章- 564 

如何在SQL查询分析器里运行查找其中一个数据库内的所有表的行数

--这一部分是用来删除生成的表的
USE [YouDataBaseName]
GO
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
posted on 2009-04-13 22:07 石川 阅读(...) 评论(...) 编辑 收藏