DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to obtain quick counts of rows in all tables?

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

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE