Run Query For Each Element In Array Or List

I'm trying to monitor all connections that hit our databases. In order to do so I created this query: SELECT @@ServerName AS [ServerName] ,NAME AS DatabaseName

Solution 1:

To solve the syntax error you have to create an dynamic query using a cursor:

DECLARE@ServerNamevarchar(50), @DynamicSQL NVARCHAR(MAX)
DECLARE@myTableVariableTABLE (id INT, ServerName varchar(50))
insertinto@myTableVariablevalues(1,'[TESLABSQL01T]'),(2,'[TESLABSQL02T]')
select*from@myTableVariableDeclare VarCursor cursorforSelect ServerName from@myTableVariableOpen VarCursor
FETCH NEXT FROM VarCursor INTO@ServerName
WHILE @@FETCH_STATUS =0BEGINSET@DynamicSQL='SELECT @@ServerName AS [ServerName]
                 ,NAME AS DatabaseName 
                 ,COUNT(STATUS) AS [NumberOfConnections]
                 ,GETDATE() AS [TimeStamp]
                 ,hostname
                 ,program_name
                 ,loginame
    FROM '+@ServerName+'.master.sys.databases sd
    LEFT JOIN '+@ServerName+'.master.sys.sysprocesses sp ON sd.database_id = sp.dbid
    WHERE database_id NOT BETWEEN 1 AND 4
    GROUP BY NAME,hostname ,program_name ,loginame'EXEC (@DynamicSQL)

    FETCH NEXT FROM VarCursor INTO@ServerNameENDCLOSE VarCursor
DEALLOCATE VarCursor

But for this query you have to ensure that you have privileges in all your servers or that you have linked servers in your principal SQL Server engine