Skip to main content

Posts

Showing posts from January, 2014

Google Search

Custom Search

Live Search

Yahoo Search


Search the web Search this site

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.



SELECTsysobjects.nameAS[Table Name],MAX(sysindexes.rows)AS[Number of Records],sysobjects.crdateAS[Created Date],sysobjects.refdateAS[Referenced Date]FROMsysobjectsINNERJOINsysindexesONsysobjects.id=sysindexes.idWHEREsysobjects.xtype='U'-- Filtering all the User Tables.ANDsysindexes.rows> 0 -- Getting all the tables having at least one reoord.GROUPBYsysobjects.name,sysobjects.crdate,sysobjects.refdateORDERBY 2 DESC-- Ordering by the number of records in table.



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