Loading ...

SQL: How to get the total number of rows in each table | CodeAsp.Net

SQL: How to get the total number of rows in each table

 /5
0 (0votes)

Yesterday a person asked on the forums on how to get the row count of all the tables in the database. I decided to write a blog on the solution which I gave to him. In order to accomplish it I have used "sp_msforeachtable" stored procedure. Let me give some brief info regarding that stored procedure.

"sp_msforeachtable" allows you to easily process a particular script against every table in a database. The "sp_MSforeachtable" stored procedure comes with SQL Server, but it is not documented in Books Online. This stored procedure exists in the "master" database and is used to process a single T-SQL command or a number of different T-SQL commands against every table in a given database.

Below is the sample script to get total number of rows. I did it for "AdventureWorks" database:

DECLARE @Temp TABLE
    (
      [TABLE_NAME] VARCHAR(80) ,
      [NO_OF_ROWS] BIGINT
    )

INSERT  INTO @Temp
EXEC sp_MSForEachtable 'SELECT ''?'' AS [TABLE_NAME],COUNT(*) AS [NO_OF_ROWS] FROM ?'

SELECT  *
FROM    @Temp


Below is the output:

 

TABLE_NAME                                                                       NO_OF_ROWS
-------------------------------------------------------------------------------- --------------------
[Production].[ProductProductPhoto]                                               504
[Sales].[StoreContact]                                                           753
[Person].[Address]                                                               19614
[Production].[ProductReview]                                                     4
[Production].[TransactionHistory]                                                113443
[Person].[AddressType]                                                           6
[Production].[ProductSubcategory]                                                37
[dbo].[AWBuildVersion]                                                           1
[Production].[TransactionHistoryArchive]                                         89253
[Purchasing].[ProductVendor]                                                     406
[Production].[BillOfMaterials]                                                   2679
[Production].[UnitMeasure]                                                       38
[Purchasing].[Vendor]                                                            104
[Purchasing].[PurchaseOrderDetail]                                               8788
[Person].[Contact]                                                               19972
[Purchasing].[VendorAddress]                                                     104
[Purchasing].[VendorContact]                                                     156
[Purchasing].[PurchaseOrderHeader]                                               4000
[Sales].[ContactCreditCard]                                                      19118
[Production].[WorkOrder]                                                         72591
[Person].[ContactType]                                                           20
[Sales].[CountryRegionCurrency]                                                  109
[Production].[WorkOrderRouting]                                                  67131
[Person].[CountryRegion]                                                         238
[Sales].[CreditCard]                                                             19118
[Production].[Culture]                                                           8
[Sales].[Currency]                                                               105
[Sales].[SalesOrderDetail]                                                       121317
[Sales].[CurrencyRate]                                                           13532
[Sales].[Customer]                                                               19185
[Sales].[SalesOrderHeader]                                                       31465
[Sales].[CustomerAddress]                                                        19220
[HumanResources].[Department]                                                    16
[Production].[Document]                                                          9
[HumanResources].[Employee]                                                      290
[dbo].[Testtable1]                                                               504
[dbo].[Testtable2]                                                               113443
[Sales].[SalesOrderHeaderSalesReason]                                            27647
[Sales].[SalesPerson]                                                            17
[HumanResources].[EmployeeAddress]                                               290
[HumanResources].[EmployeeDepartmentHistory]                                     296
[HumanResources].[EmployeePayHistory]                                            316
[Sales].[SalesPersonQuotaHistory]                                                163
[Production].[Illustration]                                                      5
[Sales].[SalesReason]                                                            10
[Sales].[Individual]                                                             18484
[Sales].[SalesTaxRate]                                                           29
[HumanResources].[JobCandidate]                                                  13
[Production].[Location]                                                          14
[Sales].[SalesTerritory]                                                         10
[Production].[Product]                                                           504
[Sales].[SalesTerritoryHistory]                                                  17
[Production].[ScrapReason]                                                       16
[HumanResources].[Shift]                                                         3
[Production].[ProductCategory]                                                   4
[Purchasing].[ShipMethod]                                                        5
[Production].[ProductCostHistory]                                                395
[Production].[ProductDescription]                                                762
[Sales].[ShoppingCartItem]                                                       3
[Production].[ProductDocument]                                                   32
[Production].[ProductInventory]                                                  1069
[Sales].[SpecialOffer]                                                           16
[Production].[ProductListPriceHistory]                                           395
[Sales].[SpecialOfferProduct]                                                    538
[Production].[ProductModel]                                                      128
[Person].[StateProvince]                                                         181
[Production].[ProductModelIllustration]                                          7
[dbo].[DatabaseLog]                                                              451
[Production].[ProductModelProductDescriptionCulture]                             762
[dbo].[ErrorLog]                                                                 0
[Sales].[Store]                                                                  701
[Production].[ProductPhoto]                                                      101 Do let me know your feedback, comments.

Comments (no comments yet)

Top Posts