SQL Server – List Out Number of Rows from all tables

To Find-Out Number Of Rows In Each Table In Database.

METHOD-1
DECLARE
@TotalRows INT,
@FirstID INT,
@table VARCHAR(255),
@cmd VARCHAR(500)

CREATE TABLE #tableData (TableName VARCHAR(255), TotalRows INT)

CREATE TABLE #temp1 (col1 INT IDENTITY, TableName VARCHAR(255))

INSERT INTO #temp1
SELECT table_name FROM Information_Schema.Tables
WHERE table_type = ‘base table’SET @TotalRows = @@ROWCOUNT

SET @FirstID = 1

WHILE @FirstID <= @TotalRows
BEGIN
SELECT @table = TableName FROM #temp1 WHERE col1 = @FirstID
SET @cmd = ‘SELECT ”’ + @table + ”’, count(*) FROM ‘ + @table

INSERT INTO #tableData
EXEC (@cmd)SET @FirstID = @FirstID + 1END

SELECT * FROM #tableData ORDER BY TotalRows DESC
DROP TABLE #tableData
DROP TABLE #temp1

METHOD-2
CREATE TABLE #tableInfo
(
TableName VARCHAR(255),
TotalRows INT
)

INSERT #tableInfo
EXEC sp_msFOREachTable
‘SELECT PARSENAME(”?”, 1),
COUNT(*) FROM ?’SELECT * FROM #tableInfo ORDER BY TotalRows DESC

DROP TABLE #tableInfo

METHOD-3
Using A Cursor_

DECLARE @table VARCHAR(255), @cmd VARCHAR(500)

CREATE TABLE #tableData (TableName VARCHAR(255), TotalRows INT)

DECLARE cursor_tableData CURSOR FOR
SELECT table_name FROM Information_Schema.Tables
WHERE table_type = ‘base table’

OPEN cursor_tableData

FETCH NEXT FROM cursor_tableData INTO @table
WHILE @@fetch_status = 0
BEGIN
SET @cmd = ‘SELECT ”’ + @table + ”’, COUNT(*) FROM ‘ + @table

INSERT INTO #tableData EXEC (@cmd)
FETCH NEXT FROM cursor_tableData INTO @table
END
CLOSE cursor_tableData
DEALLOCATE cursor_tableData

SELECT * FROM #tableData ORDER BY TotalRows DESC
DROP TABLE #tableData