Tag: sp_msforeachtable

I will count you all – Return row count for all tables

amikolaj 2 Comments

I’ve built some pretty rad SSIS packages to move massive amounts of data from an older ingres database system into SQL.

My package moves millions and millions of records in numerous amounts of tables in about 15-20 minutes.

When I was testing this sucker, I really wanted to monitor how many rows were getting inserted into each table. Since I am impatient and need a fresh slice of status all the time, I created this little diddy to return all row counts for all tables:

IF Object_id('tempdb..#count_table') IS NOT NULL
  DROP TABLE #count_table 
 
CREATE TABLE #count_table
  (
     TABLE_NAME VARCHAR(100),
     ROW_COUNT  INT
  ) 
 
INSERT INTO #count_table
EXEC sys.Sp_msforeachtable ' SELECT ''?'', count(*) from ?' 
 
SELECT *
FROM   #count_table
ORDER  BY ROW_COUNT DESC

Anyone know a better way?

UPDATE: I’ve tried another method below. This should should be faster

IF OBJECT_ID('tempdb..#sizeo') IS NOT NULL 
DROP TABLE #sizeo
 
CREATE TABLE #sizeo (
NAME VARCHAR(100),
ROWS INT,
reserved VARCHAR(50),
DATA VARCHAR(50),
index_size VARCHAR(50), 
unused VARCHAR(50)
)
 
EXEC sp_msforeachtable '
insert into #sizeo
EXEC sp_spaceused ''?''
'
SELECT name, ROWS FROM #sizeo