提供行计数和表名的脚本。

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脚本吗?

6 个解决方案

#1

If you're on SQL Server 2005 or newer (you unfortunately didn't specify which version of 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......

#2

-- 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

#3

exec sp_MSForEachTable 'SELECT ''?'' as TableName, COUNT(*) as Rows FROM ?'

#4

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 server 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';

#5

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

#6

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 updateusage might take some time but results will be 100% actual. Skip it if you need speed over accuracy.

dbcc updateusage可能需要一些时间，但是结果将是100%实际的。如果你需要的是速度而不是准确度，那就跳过它。

#1