SQL Server: using table or @table in stored procedure

Question

I have a stored procedure (see below) which inserts data into a physical table and then joins information with sys.databases. I was thinking that would it be better to not have a physical table for data insertion? Would it be better to fetch these results into a table variable within this procedure? If so, how to do that?

CREATE PROCEDURE dbo.PROC_getDbInfo
AS
    SET NOCOUNT ON
    GO
    TRUNCATE TABLE dbo.dbinfo
    GO
    EXECUTE sp_msforeachdb 'insert into dbo.dbinfo 
            select  ''?'' as name,
                    type_desc, 
                    physical_name, 
                    state_desc, 
                    size * 1.0/128 as size_in_mb, 
                    max_size, 
                    growth * 1.0/128 as growth_in_mb, 
                    is_percent_growth,
                    is_read_only    
    from [?].sys.database_files'

    GO

    SELECT  @@SERVERNAME as instance_name,
        f.name,
        d.create_date,
        d.compatibility_level,
        d.collation_name,
        d.user_access_desc,
        d.state_desc,
        d.recovery_model_desc,
        d.page_verify_option_desc,
        d.log_reuse_wait_desc,
        f.type_desc, 
        f.physical_name, 
        f.state_desc, 
        f.size_in_mb, 
        f.max_size, 
        f.growth_in_mb, 
        f.is_percent_growth,
        f.is_read_only  
    FROM dbo.dbinfo AS f INNER JOIN
            sys.databases AS d
                ON f.name = d.name
    ORDER BY f.name
GO

Answer 1

@table is better -- the table is small and the i/o cost will slow it down.

Answer 2

Table variable usage is explained here:

http://msdn.microsoft.com/en-us/library/ms175010.aspx

It basically behaves like a table when it comes to how your script looks - but has very different behaviour under the hood, and if it's small enough should not result in any disc IO.

Also, if the table is only used and then removed during the course of a procedure, this scope limitation becomes a argument for using it.

Answer 3

You'll have to use a table. Either global temp (##) or a normal table.

A table variable will not be in scope for the sp_msforeachdb call if declared for the stored proc, and not visible to the stored proc if declared in sp_msforeachdb