如何获取SQL SERVER数据库中所有表的行数

Question

我正在搜索可用于确定给定数据库的任何表中是否有任何数据（即行数）的SQL脚本。

这个想法是重新体现数据库，以防存在任何行（在任何数据库中）。

所说的数据库是Microsoft SQL SERVER。

有人可以建议一个示例脚本？

Answer 1

下面的SQL将让你的所有表的行数在数据库：

CREATE TABLE #counts 
(
    table_name varchar(255), 
    row_count int 
) 

EXEC sp_MSForEachTable @command1='INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?' 
SELECT table_name, row_count FROM #counts ORDER BY table_name, row_count DESC 
DROP TABLE #counts 

输出将表及其行数的列表。

如果你只是想在整个数据库中的总行数，附加：

SELECT SUM(row_count) AS total_row_count FROM #counts 

将让你对中行的整个数据库总数的单个值。

Answer 2

SQL Server 2005或更高版本提供了一个相当不错的报告，显示表格大小 - 包括行数等。它在标准报告中 - 它是按表格使用的光盘。

编程，还有在一个不错的解决方案： http://www.sqlservercentral.com/articles/T-SQL/67624/

Answer 3

选择从INFORMATION_SCHEMA.TABLES所有行查看，并发出已经从该视图返回的每个条目的COUNT（*）语句。

declare c_tables cursor fast_forward for 
select table_name from information_schema.tables 

open c_tables 
declare @tablename varchar(255) 
declare @stmt nvarchar(2000) 
declare @rowcount int 
fetch next from c_tables into @tablename 

while @@fetch_status = 0 
begin 

    select @stmt = 'select @rowcount = count(*) from ' + @tablename 

    exec sp_executesql @stmt, N'@rowcount int output', @[email protected] OUTPUT 

    print N'table: ' + @tablename + ' has ' + convert(nvarchar(1000),@rowcount) + ' rows' 

    fetch next from c_tables into @tablename 

end 

close c_tables 
deallocate c_tables 

Answer 4

我会对弗雷德里克的解决方案做一些小改动。我将使用sp_spaceused系统存储过程，该过程也将包含数据和索引大小。

declare c_tables cursor fast_forward for 
select table_name from information_schema.tables 

open c_tables 
declare @tablename varchar(255) 
declare @stmt nvarchar(2000) 
declare @rowcount int 
fetch next from c_tables into @tablename 

while @@fetch_status = 0 
begin 

    select @stmt = 'sp_spaceused ' + @tablename 

    exec sp_executesql @stmt 

    fetch next from c_tables into @tablename 

end 

close c_tables 
deallocate c_tables 
 

Answer 5

如果您想通过传递时间和资源来计算（*）您的3百万行表。通过Kendal Van Dyke的SQL SERVER Central试用这个。

---

行数使用的sysindexes 如果你使用SQL 2000，你将需要使用sysindexes中，像这样：

-- Shows all user tables and row counts for the current database 
-- Remove OBJECTPROPERTY function call to include system objects 
SELECT o.NAME, 
    i.rowcnt 
FROM sysindexes AS i 
    INNER JOIN sysobjects AS o ON i.id = o.id 
WHERE i.indid < 2 AND OBJECTPROPERTY(o.id, 'IsMSShipped') = 0 
ORDER BY o.NAME 

如果您使用SQL 2005或2008查询sysindexes中仍然会工作但Microsoft建议的sysindexes可以在SQL Server的未来版本中删除，以便为一个很好的做法，你应该使用动态管理视图代替，就像这样：

-- Shows all user tables and row counts for the current database 
-- Remove is_ms_shipped = 0 check to include system objects 
-- i.index_id < 2 indicates clustered index (1) or hash table (0) 
SELECT o.name, 
    ddps.row_count 
FROM sys.indexes AS i 
    INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID 
    INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID 
    AND i.index_id = ddps.index_id 
WHERE i.index_id < 2 AND o.is_ms_shipped = 0 ORDER BY o.NAME 

Answer 6

这里有一个DYNAM IC SQL做法，也让你的架构，以及：

DECLARE @sql nvarchar(MAX) 

SELECT 
    @sql = COALESCE(@sql + ' UNION ALL ', '') + 
     'SELECT 
      ''' + s.name + ''' AS ''Schema'', 
      ''' + t.name + ''' AS ''Table'', 
      COUNT(*) AS Count 
      FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) 
    FROM sys.schemas s 
    INNER JOIN sys.tables t ON t.schema_id = s.schema_id 
    ORDER BY 
     s.name, 
     t.name 

EXEC(@sql) 

如果需要的话，这将是微不足道的扩展，以在实例（加盟sys.databases）运行在所有数据库。

Answer 7

这是SQL 2008我最喜欢的解决方案，这使成果转化，我可以用它来进行排序，并得到我需要的结果“TEST”临时表：

SET NOCOUNT ON 
DBCC UPDATEUSAGE(0) 
DROP TABLE #t; 
CREATE TABLE #t 
( 
[name] NVARCHAR(128), 
[rows] CHAR(11), 
reserved VARCHAR(18), 
data VARCHAR(18), 
index_size VARCHAR(18), 
unused VARCHAR(18) 
) ; 
INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?''' 
SELECT * INTO TEST FROM #t; 
DROP TABLE #t; 
SELECT name, [rows], reserved, data, index_size, unused FROM TEST \ 
WHERE ([rows] > 0) AND (name LIKE 'XXX%') 

Answer 8

不要使用SELECT COUNT(*) FROM TABLENAME因为这是一项资源密集型操作。应该使用SQL Server Dynamic Management Views或System Catalogs来获取数据库中所有表的行计数信息。

Answer 9

这个看起来比我认为的其他人好。

USE [enter your db name here] 
GO 

SELECT  SCHEMA_NAME(A.schema_id) + '.' + 
     A.Name, SUM(B.rows) AS 'RowCount' 
FROM  sys.objects A 
INNER JOIN sys.partitions B ON A.object_id = B.object_id 
WHERE  A.type = 'U' 
GROUP BY A.schema_id, A.Name 
GO 

Answer 10

SELECT 
    sc.name +'.'+ ta.name TableName, SUM(pa.rows) RowCnt 
FROM 
    sys.tables ta 
INNER JOIN sys.partitions pa 
    ON pa.OBJECT_ID = ta.OBJECT_ID 
INNER JOIN sys.schemas sc 
    ON ta.schema_id = sc.schema_id 
WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0) 
GROUP BY sc.name,ta.name 
ORDER BY SUM(pa.rows) DESC 

Answer 11

简短而亲切

sp_MSForEachTable 'DECLARE @t AS VARCHAR(MAX); 
SELECT @t = CAST(COUNT(1) as VARCHAR(MAX)) 
+ CHAR(9) + CHAR(9) + ''?'' FROM ? ; PRINT @t' 

输出：

Answer 12

SELECT 
      SUM(sdmvPTNS.row_count) AS [DBRows] 
    FROM 
      sys.objects AS sOBJ 
      INNER JOIN sys.dm_db_partition_stats AS sdmvPTNS 
       ON sOBJ.object_id = sdmvPTNS.object_id 
    WHERE 
      sOBJ.type = 'U' 
      AND sOBJ.is_ms_shipped = 0 
      AND sdmvPTNS.index_id < 2 
    GO 

Answer 13

作品在Azure上，并不需要存储的特效。

SELECT t.name, s.row_count from sys.tables t 
JOIN sys.dm_db_partition_stats s 
ON t.object_id = s.object_id 
AND t.type_desc = 'USER_TABLE' 
AND t.name not like '%dss%' 
AND s.index_id IN (0,1) 

Credit。