I have a many customer databases. I need to execute different type of SELECT queries on each customerDb: each SELECT will extract just one value.

Then I want to put each SELECT result for each CustomerDB in a #tempTable and finally collect all resultset in one-shot for all CustomerDBs in a table like this:

I've tried to use the EXEC(@query) to redirect the result into a variable and then use the variabile into an INSERT command but it doesn't work. Any help???

Here is my script:

DECLARE c_db_names CURSOR FOR SELECT name FROM sys.databases WHERE name NOT IN('master', 'model','msdb','tempdb')
DECLARE @db_name NVARCHAR (150)
DECLARE @ServerName nvarchar(50) = @@SERVERNAME
CREATE TABLE  #temp_tb( [serverName] [varchar](50) NULL, [tenantCode] [varchar](10) NULL, [value1] [varchar](10) NULL, [value2] [varchar](10) NULL )
OPEN c_db_names
FETCH c_db_names INTO @db_name
WHILE @@Fetch_Status = 0
    BEGIN
    DECLARE @firtsQuery NVARCHAR(MAX)
    DECLARE @secondQuery NVARCHAR(MAX)
    DECLARE @firtsResult NVARCHAR(10)
    DECLARE @secondResult NVARCHAR(10)

    SELECT @firtsQuery = 'select count(*) FROM ['   @db_name   '].[dbo].table1 where ...'
    SELECT @secondQuery = 'select count(*) FROM ['   @db_name   '].[dbo].table2 where ...'

    exec @firstResult = (@firtsQuery)
    exec @secondResult = (@secondQuery)
    
    INSERT INTO #temp_tb (serverName,tenantCode,value1,value2) VALUES (@ServerName, @db_name, @firstResult, @secondResult)
FETCH c_db_names INTO @db_name
END
CLOSE c_db_names
DEALLOCATE c_db_names

SELECT * FROM #TEMP_TB
DROP TABLE #TEMP_TB

CodePudding user response：

You may want to have a play with something like the below, this goes through each database excluding the ones in the NOT IN.

This example goes through each database making sure we have the right users added.

EXEC sp_MSforeachdb N'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'')
BEGIN
    USE [?]
    IF USER_ID(''NT AUTHORITY\NETWORK SERVICE'') IS NULL
    BEGIN
        PRINT ''Using Database ''   DB_NAME()

        PRINT ''Creating database user NT AUTHORITY\NETWORK SERVICE'' 
        CREATE USER [NT AUTHORITY\NETWORK SERVICE] FOR LOGIN [NT AUTHORITY\NETWORK SERVICE]

        PRINT ''Granting db_owner permission NT AUTHORITY\NETWORK SERVICE''  
        EXEC sp_addrolemember N''db_owner'', N''NT AUTHORITY\NETWORK SERVICE''
    END
END';