SQL Server - List Out Number of Rows in Database

To List-Out The Number Of Rows In Each Table From The Current Database.
To Find-Out Number Of Rows In Each Table In Database.

/****************/
--METHOD-1
/****************/
Using A Procedure_
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 = @FirstIDSET @cmd = 'SELECT ''' + @table + ''', count(*) FROM ' + @tableINSERT INTO #tableData EXEC (@cmd)SET @FirstID = @FirstID + 1END

SELECT * FROM #tableData ORDER BY TotalRows DESC

DROP TABLE #tableData
DROP TABLE #temp1

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-4
/****************/
CREATE TABLE #tableInfo
(
TableName VARCHAR(255),
TotalRows INT
)

EXEC sp_msFOREachTable
'INSERT INTO #tableInfo
SELECT ''?'', COUNT(*) FROM ?'

SELECT * FROM #tableInfo ORDER BY TotalRows DESC

DROP TABLE #tableInfo

/****************/
--METHOD-2
/****************/
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


The following methods are using un-documented stored procedure 'sp_msFOREachTable'. There is a cursor declared in that which loops through each table in the current database and executes a script that you define.

/****************/
--METHOD-3
/****************/