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
SET NOCOUNT ON
TRUNCATE TABLE dbo.dbinfo
EXECUTE sp_msforeachdb 'insert into dbo.dbinfo
select ''?'' as name,
size * 1.0/128 as size_in_mb,
growth * 1.0/128 as growth_in_mb,
SELECT @@SERVERNAME as instance_name,
FROM dbo.dbinfo AS f INNER JOIN
sys.databases AS d
ON f.name = d.name
ORDER BY f.name
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
@table is better -- the table is small and the i/o cost will slow it down.
Table variable usage is explained here:
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.