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

try to use a temp table instead. the @table variable is out of scope in context of the dynamic sql. — Jeremy
– Jeremy, Commented Jun 7, 2017 at 14:09
what are you really trying to do? you can get all database names by selecting from sys.databases learn.microsoft.com/en-us/sql/relational-databases/… — Jeremy
– Jeremy, Commented Jun 7, 2017 at 14:14
Hi, that idea was to pull back needed information from a table from each of my child database and compare the results for inconsistencies. — Max
– Max, Commented Jun 8, 2017 at 8:05
> does not pull back results ether `DECLARE @command varchar(1000) DECLARE @SQLStatment varchar(1000) drop table #temp create table #Temp ( 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 master.. #Temp 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 #Temp ' — Max
– Max, Commented Jun 8, 2017 at 8:10

Jeremy · Accepted Answer · 2017-06-08 13:04:20Z

0

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

I don't think the "USE" statement can be dynamic (could be wrong)
The SQL is declared after you are trying to use it.
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

answered Jun 8, 2017 at 13:04

Jeremy

4,8582 gold badges23 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Max Over a year ago

Thank you so much, I never though of doing it this way. I will upload my edited version as i was added a couple extra things in. still a couple of issues i need to sort out...

Max · Accepted Answer · 2017-06-09 13:36:14Z

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

Collectives™ on Stack Overflow

sp_MSForeachdb inserting into declared virtual table issue

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related