|
The following
Microsoft SQL Server T-SQL queries will yield fast (not real time) row counts in each table in the database:
-- SQL quick table row counts
USE Northwind;
SELECT TableName = o.name,
Rows = max(i.rows)
FROM sysobjects o
INNER JOIN sysindexes i
ON o.id = i.id
WHERE xtype = 'u'
AND OBJECTPROPERTY(o.id,N'IsUserTable') = 1
GROUP BY o.name
ORDER BY Rows DESC
GO
/* Results
TableName Rows
Order Details 2155
Orders 830
Customers 91
Products 77
Territories 53
EmployeeTerritories 49
Suppliers 29
Employees 9
Categories 8
Region 4
Shippers 3
CustomerCustomerDemo 0
CustomerDemographics 0
*/
-- SQL quick table row counts - SQL Server 2005, SQL Server 2008
USE AdventureWorks2008;
SELECT TableName = SCHEMA_NAME(schema_id)+'.'+o.name,
Rows = max(i.rows)
FROM sys.sysobjects o
INNER JOIN sys.sysindexes i
ON o.id = i.id
INNER JOIN sys.objects oo
ON o.id = oo.object_id
WHERE xtype = 'u'
AND OBJECTPROPERTY(o.id,N'IsUserTable') = 1
GROUP BY schema_id, o.name
ORDER BY Rows DESC
GO
/* Partial results
TableName Rows
Sales.SalesOrderDetail 121317
Production.TransactionHistory 113443
Production.TransactionHistoryArchive 89253
Production.WorkOrder 72591
Production.WorkOrderRouting 67131
Sales.SalesOrderHeader 31465
Sales.SalesOrderHeaderSalesReason 27647
Person.BusinessEntity 20777
Person.EmailAddress 19972
Person.Password 19972
Person.Person 19972
Person.PersonPhone 19972
Sales.Customer 19820
*/
Related link:
http://www.developmentalmadness.com/archive/2008/04/01/fast-rowcount-for-sql-2005.aspx
|
