如何计算SQL Server 2005数据库中每个表中的每条记录？

I need to know how many records are in each table in a particular database. I don't care what is in the record, they will all need to be counted.

我需要知道特定数据库中每个表中有多少条记录。我不在乎记录中的内容,他们都需要被计算在内。

I already know how to do this for a single table as SELECT Count(1) FROM [my_table_name], but this gets a little redundant for 200+ tables.

我已经知道如何为单个表执行此操作,如SELECT Count(1)FROM [my_table_name],但这对于200多个表来说有点多余。

4 个解决方案

#1

Try this: (Very fast method though potentially less precise.)

试试这个:(非常快的方法虽然可能不太精确。)

SELECT   so.name, MAX(si.rows) 
FROM     sysobjects so
JOIN     sysindexes si ON so.xtype = 'U' 
AND      si.id = OBJECT_ID(so.name) 
GROUP BY so.name 
ORDER BY 2 DESC

That's one method of many. See here for several other options.

这是许多人的一种方法。请参阅此处了解其他几个选项。

#2

From How do I get a list of SQL Server tables and their row counts?

从如何获取SQL Server表及其行数列表?

DECLARE @SQL VARCHAR(255) 
SET @SQL = 'DBCC UPDATEUSAGE (' + DB_NAME() + ')' 
EXEC(@SQL) 

CREATE TABLE #foo 
( 
    tablename VARCHAR(255), 
    rc INT 
) 

INSERT #foo 
    EXEC sp_msForEachTable 
        'SELECT PARSENAME(''?'', 1), 
        COUNT(*) FROM ?' 

SELECT tablename, rc 
    FROM #foo 
    ORDER BY rc DESC 

DROP TABLE #foo

#3

As has already been posted, if it was a one off thing I'd probably use sp_msforeachtable. However, it's an undocument system procedure so if that's a problem, then you can easily do it like this:

正如已经发布的那样,如果它是一次性的话,我可能会使用sp_msforeachtable。但是,这是一个非文档系统程序,所以如果这是一个问题,那么你可以轻松地这样做:

DECLARE @SQL VARCHAR(MAX)
SET @SQL = ''

SELECT @SQL = 
    @SQL + 'SELECT ''' + TABLE_NAME + ''' AS TableName, COUNT(*) AS RecordCount 
            FROM [' + TABLE_NAME + '];' + CHAR(13)
FROM INFORMATION_SCHEMA.TABLES

EXECUTE (@SQL)

#4

I'm sure you can get something better than this but this should do in a pinch. For the record, sp_spaceused is your friend in queries such as this!

我相信你可以得到比这更好的东西,但这应该在紧要关头。对于记录,sp_spaceused是你这样的查询中的朋友!

DECLARE @tbls table (TableID int identity, TableName varchar(max))

INSERT INTO @tbls
SELECT t1.name
FROM sysobjects t1
INNER JOIN sysindexes t2
ON t1.id = t2.id
WHERE t2.indid <= 1
AND t2.rows > 0
AND OBJECTPROPERTY(t1.id,'IsUserTable') = 1

DECLARE @tblcount int

SELECT @tblcount=COUNT(1)
FROM @tbls

DECLARE @results table (name varchar(max), [rows] bigint, reserved varchar(max), data varchar(max), index_size varchar(max), unused varchar(max))

DECLARE @counter int
SET @counter = 0
DECLARE @tblname varchar(max)

while @counter < @tblcount
begin
    SELECT @tblname=TableName FROM @tbls WHERE TableID=@counter+1

    INSERT INTO @results
    exec sp_spaceused @tblname

    SET @counter = @counter+1
end

SELECT * FROM @results

#1