如何列出数据库中每个表的行数。相当于
select count(*) from table1
select count(*) from table2
...
select count(*) from tableN
我会张贴一个解决方案，但其他方法是受欢迎的
当前回答
SELECT
T.NAME AS 'TABLE NAME',
P.[ROWS] AS 'NO OF ROWS'
FROM SYS.TABLES T
INNER JOIN SYS.PARTITIONS P ON T.OBJECT_ID=P.OBJECT_ID;
其他回答
Shnugo的答案是唯一一个工作在Azure的Externa表。(1) Azure SQL根本不支持sp_MSforeachtable;External表的partitions总是0。
你可以试试这个:
SELECT OBJECT_SCHEMA_NAME(ps.object_Id) AS [schemaname],
OBJECT_NAME(ps.object_id) AS [tablename],
row_count AS [rows]
FROM sys.dm_db_partition_stats ps
WHERE OBJECT_SCHEMA_NAME(ps.object_Id) <> 'sys' AND ps.index_id < 2
ORDER BY
OBJECT_SCHEMA_NAME(ps.object_Id),
OBJECT_NAME(ps.object_id)
这种方法使用字符串连接来动态生成包含所有表及其计数的语句，就像原始问题中给出的示例:
SELECT COUNT(*) AS Count,'[dbo].[tbl1]' AS TableName FROM [dbo].[tbl1]
UNION ALL SELECT COUNT(*) AS Count,'[dbo].[tbl2]' AS TableName FROM [dbo].[tbl2]
UNION ALL SELECT...
最后用EXEC执行:
DECLARE @cmd VARCHAR(MAX)=STUFF(
(
SELECT 'UNION ALL SELECT COUNT(*) AS Count,'''
+ QUOTENAME(t.TABLE_SCHEMA) + '.' + QUOTENAME(t.TABLE_NAME)
+ ''' AS TableName FROM ' + QUOTENAME(t.TABLE_SCHEMA) + '.' + QUOTENAME(t.TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES AS t
WHERE TABLE_TYPE='BASE TABLE'
FOR XML PATH('')
),1,10,'');
EXEC(@cmd);
从这个问题中: https://dba.stackexchange.com/questions/114958/list-all-tables-from-all-user-databases/230411#230411
我在@Aaron Bertrand提供的答案中添加了记录计数，该答案列出了所有数据库和所有表。
DECLARE @src NVARCHAR(MAX), @sql NVARCHAR(MAX);
SELECT @sql = N'', @src = N' UNION ALL
SELECT ''$d'' as ''database'',
s.name COLLATE SQL_Latin1_General_CP1_CI_AI as ''schema'',
t.name COLLATE SQL_Latin1_General_CP1_CI_AI as ''table'' ,
ind.rows as record_count
FROM [$d].sys.schemas AS s
INNER JOIN [$d].sys.tables AS t ON s.[schema_id] = t.[schema_id]
INNER JOIN [$d].sys.sysindexes AS ind ON t.[object_id] = ind.[id]
where ind.indid < 2';
SELECT @sql = @sql + REPLACE(@src, '$d', name)
FROM sys.databases
WHERE database_id > 4
AND [state] = 0
AND HAS_DBACCESS(name) = 1;
SET @sql = STUFF(@sql, 1, 10, CHAR(13) + CHAR(10));
PRINT @sql;
--EXEC sys.sp_executesql @sql;
如果您使用MySQL >4。X你可以用这个:
select TABLE_NAME, TABLE_ROWS from information_schema.TABLES where TABLE_SCHEMA="test";
请记住，对于某些存储引擎，TABLE_ROWS只是一个近似值。
