sp_MSForeachdb inserting into declared virtual table issue

Question

I am having issues getting information to insert into the @TBL2 Table.

what am i doing wrong?

DECLARE @command varchar(1000) 
DECLARE @SQLStatment varchar(1000) 

DECLARE @TBL2 table (
                    Database_Name nvarchar(max),
                    SI_SITE nvarchar(max),
                    SI_DB_USER nvarchar(max)
                    )  

SELECT @command = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') BEGIN USE ? insert into @tbl2  EXEC('+ @SQLStatment +') END'

set @SQLStatment =  'select top 1 Db_Name() as Database_Name, SI_SITE, SI_DB_USER from t_site'

EXEC master.. sp_MSForeachdb @command

select * from @TBL2

Answer 1

Try This: There are several issues with the approach you are taking:

1. I don't think the "USE" statement can be dynamic (could be wrong)

2. The SQL is declared after you are trying to use it.

3. sp_msforeachdb is undocumented and shouldn't be relied on, even though it can work in many circumstances.

My approach uses the sys.databases and string concatenation to generate the appropriate SQL string to get the data you want from each table in all databases except the system databases, then executes the results into a temp table. The approach also assumes dbo schema. Adjust if necessary.

declare @SQL nvarchar(max)
set @SQL = ''

Create Table #TBL2 (
                    Database_Name nvarchar(max),
                    SI_SITE nvarchar(max),
                    SI_DB_USER nvarchar(max)
                    )

Select @SQL = @SQL + 'INSERT INTO #TBL2 (Database_Name, SI_SITE, SI_DB_USER) select top 1 ''' + name + ''' as Database_Name, SI_SITE, SI_DB_USER from ' + name + '..t_site;' + char(13)  
From sys.databases
Where name not in ('master', 'model', 'msdb', 'tempdb') 

print @SQL                      
exec sp_executesql @SQL

Select * From #TBL2

drop table #TBL2