Hello folks,
Today I will show you how we can find out all the tables present in a database along with their row count. This can be easily done with the help of system tables. Let me first write the query and then discuss about the same in detail.
USE[SampleDatabase]
Select tbl.name, si.rows from sysindexes si
inner join sys.tables tbl on si.id = tbl.object_id and indid < 2
Sys.table has information about all the tables in a given database. While sysindexes has information of all the indexes present in database.
Key terms present inthe query:
si.rows of thesysindexes give the row count associated with the given index.
Tbl.name gives the table name and comes from the sys.table.
si.id and tbl.object_id gives the unique ID associated with each table present in the database.
Indid this stands for the ID of the index, 1 is for clustered index and (> 1) is for nonclustered index.
Note: By default all the primary key have clustered index associated with it.
Store Procedure
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
CREATE PROCEDURE AllTableWithRowCount
AS
BEGIN
SET NOCOUNT ON;
SELECT tbl.name, si.rows FROM sysindexes si
INNER JOIN sys.tables tbl on si.id = tbl.object_id and indid < 2
END
select tbl.name, dd.rows from sysindexes dd
inner join sysobjects tbl on dd.id = tbl.id where dd.indid < 2 and tbl.xtype = 'U'