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
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
/****************/
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
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
/****************/
No comments:
Post a Comment