提供行数和表名的脚本

Question

也许您很容易地说我如何提供表名和行数？

伪SQL：

for "select tablename from system.Tables" into :tablename
  execute "select count(*) from ? into ?" using :tablename, :count
  return row(:tablename, :count)
end for

您能告诉我在T-SQL中显示此脚本吗？

Answer 1

如果您使用的是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

产生的输出类似（来自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......