skreebydba

Right now I am working on a process to transfer a single table between databases. There are over 36 million rows in the source table, and as a result I am batching the inserts in a loop. I need the upper limit of my loop, which is equal to the number of rows in the source table. Usually, I would set the @rowlimit variable using a line of code similar to this:

SELECT @rowlimit = COUNT(*) FROM sourcetable

In the case of the 36 million rows, the SELECT COUNT(*) was taking far too long. Fortunately, there is an alternative in the sp_spaceused system stored procedure. Where the SELECT COUNT(*) operation was taking up to 20 minutes, the following code returns in seconds.

USE databasename
GO

CREATE TABLE #spaceused
(name NVARCHAR(128)
,rows CHAR(11)
,reserved VARCHAR(18)
,data VARCHAR(18)
,index_size VARCHAR(18)
,unused VARCHAR(18))

DECLARE @rowlimit BIGINT

INSERT INTO #spaceused
EXEC sp_spaceused tablename

SELECT @rowlimit = rows FROM #spaceused
WHERE name = 'tablename'

SELECT @rowlimit 

DROP TABLE #spaceused

I hope you find this useful.