A stored Procedure to Count Numbers of Rows in Table

This a simple Stored procedure to count the total numbers of rows in each table of your Database.
I assume you have Northwind database installed.

The Stored Procedure

IF EXISTS ( SELECT  *
FROM sysobjects
WHERE id = OBJECT_ID(N'[dbo].[sp_GetRowsCountForAllTables]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1 )
DROP PROCEDURE [dbo].[sp_GetRowsCountForAllTables]
GO

CREATE PROCEDURE sp_GetRowsCountForAllTables
@DBName VARCHAR(128) = NULL
AS
SET nocount ON
IF @DBName IS NULL
SET @DBName = DB_NAME()

CREATE TABLE #a
(
TableName VARCHAR(128)
, norows INT NULL
, id INT IDENTITY(1, 1)
)

DECLARE @id INT
, @maxID INT
, @TableName VARCHAR(128)
, @FKName VARCHAR(128)
, @cmd NVARCHAR(1000)
, @rc INT
, @spcmd VARCHAR(1000)

SET @cmd = 'exec ' + @DBName + '..sp_executesql N''insert #a (TableName)
select TABLE_NAME from information_schema.tables
where TABLE_TYPE = ''''BASE TABLE'''' ''
'
EXEC (@cmd)

SELECT @id = 0
, @maxID = MAX(id)
FROM #a

WHILE @id < @maxID
BEGIN
SELECT @id = MIN(id)
FROM #a
WHERE id > @id

SELECT @TableName = TableName
FROM #a
WHERE id = @id

SET @cmd = 'exec ' + @DBName
+ '..sp_executesql N''update #a set norows = (select rows from sysindexes where indid in (0,1) and id = object_id('''''
+ @TableName + '''''))'
SET @cmd = @cmd + ' where #a.id = ' + CONVERT(VARCHAR(10), @id)
+ ''''

EXEC (@cmd)
IF @rc <> 0
OR @@error <> 0
BEGIN
RAISERROR('failed %s',16,-1,@TableName)
RETURN
END
END

SELECT *
FROM #a ORDER BY norows desc

DROP TABLE #a
GO

The Output:

EXEC sp_GetRowsCountForAllTables

 

Sandeep

a dev, an amateur photographer and a father

 

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: