Get count of tables along with count of rows in all tables.


Execute the following Microsoft SQL Server T-SQL scripts in Management Studio Query Editor to list accurate and approximate (fast) rowcounts for all the tables in the database.

— SQL Server count all rows in all tables – sql server rowcount all tables

DECLARE  @CountStats  TABLE(

SchemaName SYSNAME,

TableName  SYSNAME,

RowsCount  INT

)

INSERT @CountStats

EXEC sp_msForEachTable

‘SELECT PARSENAME(”?”, 2), PARSENAME(”?”, 1), COUNT(*) FROM ?’

SELECT   *

FROM     @CountStats

ORDER BY RowsCount DESC

/* Partial results

SchemaName  TableName                     RowsCount

Sales       SalesOrderDetail              121317

Production  TransactionHistory            113443

Production  TransactionHistoryArchive     89253

Production  WorkOrder                     72591

*/
———-

— APPROXIMATE – very quick

— SQL count rows in all tables

USE AdventureWorks2008;

DECLARE @SQL NVARCHAR(255)

SET @SQL = ‘DBCC UPDATEUSAGE (‘ + DB_NAME() + ‘)’

EXEC sp_executeSQL @SQL

SELECT Schema_name(t.schema_id) AS SchemaName,

t.name                   AS TableName,

i.rows                   as [Rows]

FROM   sys.tables AS t

INNER JOIN sys.sysindexes AS i

ON t.object_id = i.id

AND i.indid < 2

ORDER BY [Rows] DESC

GO

/* Partial results

SchemaName  TableName                     Rows

Sales       SalesOrderDetail              121317

Production  TransactionHistory            113443

Production  TransactionHistoryArchive     89253

Production  WorkOrder                     72591

Production  WorkOrderRouting              67131

*/
———-

— SQL Server 2000 – approximate quick count

USE Northwind;

SELECT      TableName = o.name,

[Rows] = replace(convert(VARCHAR,convert(MONEY,max(i.rows)),1), ‘.00’,”)

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 max(i.rows) DESC

GO

/* Partial resutls

TableName               Rows

Order Details           2,155

Orders                  830

Customers               91

Products                77

Territories             53

EmployeeTerritories     49

*/
———-

— ACCURATE COUNT with cursor

— SQL Server find rows count in all tables and views – select count(*)

USE AdventureWorks2008;

DECLARE  @SchemaName SYSNAME,

@TableName  SYSNAME,

@TableType  varchar(12)

DECLARE  @SQL NVARCHAR(MAX)

CREATE TABLE #Population (

TableName  VARCHAR(256),

TableType varchar(12),

[Population] INT);

DECLARE curTablesAndViews CURSOR FAST_FORWARD FOR

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE

FROM   INFORMATION_SCHEMA.TABLES

OPEN curTablesAndViews

FETCH NEXT FROM curTablesAndViews

INTO @SchemaName,

@TableName,

@TableType

WHILE (@@FETCH_STATUS = 0)

BEGIN

SELECT @SQL = ‘INSERT #Population SELECT ”’ +

@SchemaName + ‘.’ + @TableName +

”’,”’+ @TableType+”’, COUNT(*) as Population ‘ +

‘FROM [‘ + @SchemaName + ‘].[‘ + @TableName + ‘]’

PRINT @SQL — debugging

EXEC SP_EXECUTESQL   @SQL

FETCH NEXT FROM curTablesAndViews

INTO @SchemaName,

@TableName,

@TableType

END

CLOSE curTablesAndViews

DEALLOCATE curTablesAndViews

— Return the list of rows counts

SELECT   *

FROM     #Population

ORDER BY [Population] DESC

GO

DROP TABLE #Population

/*

TableName                     TableType   Population

…….

Person.BusinessEntityAddress  BASE TABLE  19614

Person.Address                BASE TABLE  19614

Sales.CreditCard              BASE TABLE  19118

Sales.PersonCreditCard        BASE TABLE  19118

Sales.vIndividualCustomer     VIEW        18508

…….

*/
———-

— SQL Server 2005/2008 – approximate, very quick

DECLARE @SQL NVARCHAR(255)

SET @SQL = ‘DBCC UPDATEUSAGE (‘ + DB_NAME() + ‘)’

EXEC sp_executeSQL @SQL

SELECT schema_name(o.schema_id)    AS SchemaName,

object_name(o.object_id)    AS TableName,

SUM(row_count)              AS [Count]

FROM     sys.dm_db_partition_stats ps

INNER JOIN sys.objects o

ON o.object_id = ps.object_id

WHERE    index_id < 2

AND TYPE = ‘U’

GROUP BY o.schema_id,  o.object_id

ORDER BY [Count] desc

/*

SchemaName  TableName                     Count

Production  TransactionHistory            14520704

Sales       SalesOrderDetail              121317

Production  TransactionHistoryArchive     89253

Production  WorkOrder                     72591

*/

———-

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: