alternatives to passing temp table as a parameter to a stored procedure

some time ago I was developing a simple procedure to get the database sizes on a particular server. Here it is the procedure: USE MASTER GO ALTER PROCEDURE dbo.sp_getDBSpace @Radhe sysname=null, @system_only BIT = NULL , @user_only BIT = NULL , @database_list NVARCHAR(MAX) = NULL , @exclude_list NVARCHAR(MAX) = NULL , @recovery_model_desc NVARCHAR(120) = NULL , @compatibility_level TINYINT = NULL , @state_desc NVARCHAR(120) = N'ONLINE' , @is_read_only BIT = 0 , @is_auto_close_on BIT = NULL , @is_auto_shrink_on BIT = NULL , @is_broker_enabled BIT = NULL AS BEGIN SET NOCOUNT ON SET XACT_ABORT ON SET DEADLOCK_PRIORITY NORMAL; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED DECLARE @SQL NVARCHAR(MAX) SET @sql = N'USE [?] SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;with radhe as ( SELECT Servername = @@servername, DatabaseName = DB_NAME(), a.FILE_ID, a.type, -- 0 is log and 1 is data [Drive] = LEFT(UPPER(a.physical_name), 1), [FILE_SIZE_GB] = CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000/1024.000, 2)), [SPACE_USED_GB] = CONVERT(DECIMAL(12, 2), ROUND(FILEPROPERTY(a.NAME, ''SpaceUsed'') / 128.000/1024.000, 2)), [FREE_SPACE_GB] = CONVERT(DECIMAL(12, 2), ROUND((a.size - FILEPROPERTY(a.NAME, ''SpaceUsed'')) / 128.000/1024.000, 2)), a.NAME, a.PHYSICAL_NAME AS [FILENAME] FROM sys.database_files a ) insert into ' + @Radhe + N'([ServerName],[DatabaseName],[Drive],[FILE_ID],[FILE_SIZE_GB],[SPACE_USED_GB],[FREE_SPACE_GB],[NAME],[FILENAME],[TOTAL_DATABASE_SIZE]) SELECT [ServerName] ,[DatabaseName] ,[Drive] ,[FILE_ID] ,[FILE_SIZE_GB] ,[SPACE_USED_GB] ,[FREE_SPACE_GB] ,[NAME] ,[FILENAME] ,TOTAL_DATABASE_SIZE = SUM([FILE_SIZE_GB] ) OVER (PARTITION BY A.DATABASENAME ) --,SPACE_USED = SUM([SPACE_USED_GB] ) OVER (PARTITION BY A.DATABASENAME ) --,TOTAL_FREE_LOG_SPACE_GB = SUM(CASE WHEN A.TYPE = 0 THEN [FREE_SPACE_GB] ELSE 0 END ) OVER (PARTITION BY A.DATABASENAME ) --,TOTAL_FREE_DATA_SPACE_GB = SUM(CASE WHEN A.TYPE = 1 THEN [FREE_SPACE_GB] ELSE 0 END ) OVER (PARTITION BY A.DATABASENAME ) FROM radhe a ' exec sp_foreachdb @system_only = @system_only, @user_only = @user_only, @database_list = @database_list, @exclude_list = @exclude_list, @recovery_model_desc = @recovery_model_desc, @compatibility_level = @compatibility_level, @state_desc = @state_desc, @is_read_only = @is_read_only , @is_auto_close_on = @is_auto_close_on, @is_auto_shrink_on = @is_auto_shrink_on , @is_broker_enabled = @is_broker_enabled ,@command=@SQL END GO And here is how I used to call this procedure: IF OBJECT_ID('tempdb..#Radhe') IS NOT NULL DROP TABLE #Radhe GO CREATE TABLE #Radhe( [ServerName] NVARCHAR(128) NULL, [DatabaseName] NVARCHAR(128) NULL, [Drive] NVARCHAR(1) NULL, [FILE_ID] INT NOT NULL, [FILE_SIZE_GB] DECIMAL(12,2) NULL, [SPACE_USED_GB] DECIMAL(12,2) NULL, [FREE_SPACE_GB] DECIMAL(12,2) NULL, [NAME] SYSNAME NOT NULL, [FILENAME] NVARCHAR(260) NULL, [TOTAL_DATABASE_SIZE] DECIMAL(38,2) NULL) declare @db_list NVARCHAR(MAX) SELECT @db_list = STUFF(( SELECT ', ' + name FROM sys.databases WHERE name NOT IN ('DBA','TABLEBACKUPS','MASTER','MSDB','MODEL','TEMPDB') FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 2, '') --just to check the list --exec sp_foreachdb @database_list = @db_list -- ,@command='use ?; print db_name() + char(13)' exec sp_getDBSpace @Radhe = '#Radhe' ,@database_list = @db_list ,@exclude_list = 'DBA,TABLEBACKUPS,MASTER,MSDB,MODEL,TEMPDB' select * from #Radhe **Question:** Although it works is there a more elegant way to get this done by doing something else rather than passing a temp table name as a parameter? I need the temp table to be available after the call of the stored procedure. Inside the stored procedure, I need to insert data into that temp table. is there any other way of getting this done, without using a temp table as a parameter as I did? there is a similar question here . Some links: -- https://dba.stackexchange.com/questions/121865/how-to-restore-a-database-ignoring-the-free-space-within-each-file/121867#121867 -- https://dba.stackexchange.com/questions/163017/generate-insert-list-but-only-for-non-nullable-columns -- Determining Drive Letters in Use by SQL Server Databases -- https://www.mssqltips.com/sqlservertip/3048/determining-drive-letters-in-use-by-sql-server-databases/ -- PROCEDURE sp_foreachdb -- https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/blob/dev/sp_foreachdb.sql