Finding Number of Records in SQL Tables

By using the following query you will be able to get all the tables with at least one record in the selected database. It will be handy when you needed to get an idea of the number of records in a database. This uses two SQL Server system views, namely SysObjects and SysIndexes.

1. SELECT sysobjects.name AS [Table Name],
2.         MAX(sysindexes.rows) AS [Number of Records],
3.         sysobjects.crdate AS [Created Date],
4.         sysobjects.refdate AS [Referenced Date]
5. FROM sysobjects
6. INNER JOIN sysindexes ON sysobjects.id = sysindexes.id
7. WHERE sysobjects.xtype = ‘U’ — Filtering all the User Tables.
8.     AND sysindexes.rows > 0 — Getting all the tables having at least one reoord.
9. GROUP BY sysobjects.name,
10.     sysobjects.crdate,
11.     sysobjects.refdate
12. ORDER BY 2 DESC — Ordering by the number of records in table.

 

Above query will bring the following results on the Northwind database.

Advertisement