SQL 提供行数和表名的脚本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9954775/
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
Script that provides the row counts and table names
提问by durumdara
Maybe you easily said how to I provide table names and row counts?
也许你很容易说我如何提供表名和行数？
Pseudo SQL:
伪SQL：
for "select tablename from system.Tables" into :tablename
execute "select count(*) from ? into ?" using :tablename, :count
return row(:tablename, :count)
end for
Can you tell me show me this script in T-SQL?
你能告诉我用 T-SQL 给我看这个脚本吗？
回答by marc_s
If you're on SQL Server 2005 or newer(you unfortunately didn't specify which versionof SQL Server you're using), this query should give you that information:
如果您使用的是 SQL Server 2005 或更新版本（不幸的是，您没有指定您使用的是哪个版本的 SQL Server），则此查询应为您提供以下信息：
SELECT
TableName = t.NAME,
TableSchema = s.Name,
RowCounts = p.rows
FROM
sys.tables t
INNER JOIN
sys.schemas s ON t.schema_id = s.schema_id
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
t.is_ms_shipped = 0
GROUP BY
t.NAME, s.Name, p.Rows
ORDER BY
s.Name, t.Name
This produces an output something like (this is from AdventureWorks):
这会产生类似的输出（来自AdventureWorks）：
TableName TableSchema RowCounts
AWBuildVersion dbo 1
DatabaseLog dbo 1597
ErrorLog dbo 0
Department HumanResources 16
Employee HumanResources 290
JobCandidate HumanResources 13
Address Person 19614
AddressType Person 6
... and so on......
回答by Jitendra Sawant
SELECT
t.NAME AS TableName, p.[Rows] FROM
sys.tables t INNER JOIN
sys.partitions p ON t.object_id = p.OBJECT_ID GROUP BY
t.NAME, p.[Rows] ORDER BY t.NAME
回答by Vikram
-- 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
回答by David Brabant
exec sp_MSForEachTable 'SELECT ''?'' as TableName, COUNT(*) as Rows FROM ?'
回答by Zd8n8k
I have adjusted the answer from marc_c with CTE and displaying it with choice of displaying just the schema you are after.
我已经用 CTE 调整了来自 marc_c 的答案，并选择只显示您所追求的模式来显示它。
Should work with SQL Serve 2005 and newer.
应该与SQL Serve 2005 和更新版本一起使用。
WITH CountRowsInTables (Table_Name, Table_Schema, Row_Counts) AS
(
SELECT
TableName = t.Name,
TableSchema = s.Name,
RowCounts = p.Rows
FROM
sys.tables t
INNER JOIN
sys.schemas s ON t.schema_id = s.schema_id
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
t.is_ms_shipped = 0
GROUP BY
s.Name, t.Name, p.Rows
)
SELECT Table_name, Table_Schema, Row_Counts
FROM CountRowsInTables
WHERE Table_Schema = 'Pick_Schema_to_display';
回答by Suresh Kamrushi
This works for me:
这对我有用：
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
回答by wqw
Try this
尝试这个
-- drop table #tmpspace
create table #tmpspace (
name sysname
, rows int
, reserved varchar(50)
, data varchar(50)
, index_size varchar(50)
, unused varchar(50)
)
dbcc updateusage(0) with NO_INFOMSGS
exec sp_msforeachtable 'insert #tmpspace exec sp_spaceused ''?'''
select * from #tmpspace
order by convert(int, substring(reserved, 1, charindex(' ', reserved))) desc, rows desc, name
Works on sql2000 too.
也适用于 sql2000。
dbcc updateusagemight take some time but results will be 100% actual. Skip it if you need speed over accuracy.
dbcc updateusage可能需要一些时间，但结果将是 100% 实际的。如果您需要速度而不是准确性，请跳过它。
