SQL Server 2008：我有1000张表，我需要知道哪些表有数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3980622/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server 2008: I have 1000 tables, I need to know which tables have data
提问by l--''''''---------''''''''''''
Is there a way in SMSS to detect whether a table has any records? I need to get a list of tables that have records. perhaps there is a sql statement that will do the trick?
SMSS中有没有办法检测表是否有任何记录？我需要获取有记录的表的列表。也许有一个 sql 语句可以解决问题？
采纳答案by Noel Abrahams
A simpler syntax:
更简单的语法：
SELECT [Name] = o.name
, [RowCount] = SUM(p.row_count)
FROM SYS.DM_DB_PARTITION_STATS p
INNER JOIN
SYS.TABLES o
ON p.[object_ID] = o.[object_id]
WHERE index_id <= 1 -- Heap or clustered index only
GROUP BY o.name
ORDER BY 2 desc
回答by marc_s
Try this - gives you the table name and the row count:
试试这个 - 给你表名和行数：
SELECT
t.NAME AS TableName,
SUM(p.rows) AS [RowCount]
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
WHERE
i.index_id <= 1
GROUP BY
t.NAME, i.object_id, i.index_id, i.name
ORDER BY
SUM(p.rows) DESC
It shows all tables and their row counts in a single output.
它在单个输出中显示所有表及其行数。
回答by Martin Smith
As your question specifically mentions SSMS you can also right click the database in object explorer and then from the short cut menu do
由于您的问题特别提到 SSMS，您也可以右键单击对象资源管理器中的数据库，然后从快捷菜单中执行
Reports -> Standard Reports -> Disc Usage By Table
回答by Gabriel McAdams
You can use this stored procedure:
您可以使用此存储过程：
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"
This will return a resultset for each table in the database (each showing the name, and the number of rows, among other information).
这将为数据库中的每个表返回一个结果集（每个表都显示名称、行数以及其他信息）。
Here is how you can put them into a table variable, and order them by the number of rows:
以下是如何将它们放入表变量中，并按行数对它们进行排序：
DECLARE @TBL TABLE (
[name] nvarchar(500),
[rows] bigint,
[reserved] nvarchar(500),
[data] nvarchar(500),
[index_size] nvarchar(500),
[unused] nvarchar(500)
)
INSERT INTO @TBL
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"
SELECT * FROM @TBL
ORDER BY [rows] DESC
回答by Nitika Chopra
Hope, It helps you-
希望，它可以帮助你-
SELECT name AS [TableList] FROM SYS.DM_DB_PARTITION_STATS s
INNER JOIN sys.tables t ON t.[object_id] = s.[object_id]
WHERE row_count = 0
This code shows that list of tables, which does not contain any data or row.
此代码显示不包含任何数据或行的表列表。
Thanks!!!
谢谢！！！
