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

Answer 2

I still have some issues to work out but this is what it may look like

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

Create Table #TBL3 (
                Server_Name nvarchar(max),
                Database_Name nvarchar(max),
                DB_Owner nvarchar(max),
                SI_SITE nvarchar(max),
                SI_DB_USER nvarchar(max),
                DB_Creation_date nvarchar(max),
                DB_state nvarchar(max),
                DB_SQL_version nvarchar(max)
                )

Select @SQL = @SQL + 'INSERT INTO #TBL3 
(
     Server_Name
    ,[Database_Name]
    ,[DB_Owner]
    ,[DB_Creation_Date]
    ,[DB_State]
    ,[DB_SQL_Version]       
    ,[SI_SITE]
    ,[SI_DB_USER]
)

SELECT 
     Server_Name
    ,quotename(''' + name + ''')
    ,[DB_Owner]
    ,[DB_Creation_date]
    ,[DB_state]
    ,[DB_SQL_version]       
    ,[SI_SITE]
    ,[SI_DB_USER]

From( SELECT TOP 1
         [SI_SITE]
        ,[SI_DB_USER]
      From  [' + name + ']..[t_site]) Q1,

    ( SELECT
        @@SERVERNAME as [Server_Name]
        ,suser_sname(owner_sid) as [DB_Owner]
        ,[Create_Date] as [DB_Creation_date]
        ,[state_desc] as [DB_state]
        ,case  [compatibility_level] 
        when 80 then ''SQL Server 2000''  when 90 then ''SQL Server 2005''  when 100 then ''SQL Server 2008''   when 110 then ''SQL Server 2012''   when 120 then ''SQL Server 2014''   when 130 then ''SQL Server 2016''   when 140 then ''SQL Server 2017''   else cast(compatibility_level as varchar(100)) end as DB_SQL_version
    from [sys].[databases]
    where [name] = ''' + name + '''
    ) Q2;' + char(13)

    From sys.databases
Where name not in ('master', 'model', 'msdb', 'tempdb') 
and name not in ('DBX') -- where [T_site] table does not exist  
and name not in ('DBY')  -- offline, need offline clause added


    print @SQL   

    exec sp_executesql @SQL

    Select * From #TBL3

    DROP TABLE #TBL3