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