Using sp_spaceused on a temporary table-CodePudding

All I want to do is search through a subscribers data (there are many more temp tables I want to check the data of) and just directly insert the results from sp_spaceused on a temp table into another temp table. Then after it has finished counting all the data space and rows from about 100 different temp tables, to then output or calculate the results

SELECT DISTINCT
    t1.SubscriberGUID,
    t1.ItemGUID_Entity,
    t1.SubscriberID,
    t1.SubscriberRegionID,
    t1.SubscriberTypeID,
    t1.ID,
    t1.SubscriberNameFull,
    t1.SubscriberEmail,
    t1.SubscriberLogin,
    t1.SubscriberPassword,
    t1.Active,
    t1.DateCreated,
    t1.DateDeleted
INTO #Found_Subscriber
FROM (
        SELECT t100.*, ROW_NUMBER() OVER(Order BY (SELECT 1)) AS 'RowNumber'
        FROM
            #AllSubscribers t100
    ) AS t1
WHERE t1.RowNumber = @I

CREATE TABLE #FileSize
    (
        [name] NVARCHAR(128),
        [rows] INT,
        [reserved] VARCHAR(18),
        [data] VARCHAR(18),
        [index_size] VARCHAR(18),
        [unused] VARCHAR(18)
    )
    

INSERT INTO #FileSize exec sp_spaceused #Found_Subscriber
INSERT INTO #FileSize exec sp_spaceused #Found_SubscriberInfo
...

But this is not working. It throws an error saying:

Msg 15009, Level 16, State 1, Procedure sp_spaceused, Line 120 [Batch Start Line 0] The object '#Found_Subscriber' does not exist in database 'd1' is invalid for this operation.

CodePudding user response：

The error message is telling you that you're calling sp_spaceused in the wrong database. Try:

EXEC tempdb.sys.sp_spaceused N'#Found_Subscriber';