DECLARE 
  @sql              NVARCHAR(2000),
  @sql_from_db      NVARCHAR(2000),
  @ServerName       VARCHAR(2000),
  @OutputColumns    VARCHAR(MAX),
  @OutputTableName  VARCHAR(200),
  @Counter          INT,
  @MaxCounter       INT 
  
DECLARE @table TABLE (
  ID INT IDENTITY(1, 1), 
  ServerName VARCHAR(500), 
  Query VARCHAR(max),
  OutputColumns VARCHAR(2000),
  OutputTableName VARCHAR(200)
)

INSERT INTO @table (ServerName, Query, OutputColumns, OutputTableName)
SELECT 
  ServerName,
  Query,
  OutputColumns,
  OutputTableName
FROM dbo.servers AS s
  INNER JOIN dbo.server_queries AS sq
    ON s.ServerID = sq.ServerID
  INNER JOIN dbo.queries AS q
    ON q.QueryID = sq.QueryID
    
SET @MaxCounter = (SELECT COUNT(*) FROM @table)
SET @Counter = 1

WHILE @Counter <= @MaxCounter
  BEGIN 
    SELECT 
      @sql_from_db = Query,
      @ServerName = ServerName,
      @OutputColumns = OutputColumns,
      @OutputTableName = OutputTableName
    FROM @table
    WHERE ID = @Counter
    
    SET @sql = N'
      INSERT INTO ' + @OutputTableName + '
      SELECT ''' + @ServerName + ''', ' + @OutputColumns + '
      FROM OPENROWSET(''SQLNCLI'', ''' + @ServerName + '''; ''username''; ''password'', ''' +
        REPLACE(@sql_from_db, CHAR(39), CHAR(39)+CHAR(39)) + '
        '')'
    
    --PRINT @sql
    EXEC sp_executesql @sql
    SET @Counter = @Counter + 1
    
  END

quote:

---

Originally posted by RickD
I am not sure where the information on the backups would come from though as this information is held in the job itself or the maintenence plan if you are using them.

---

SELECT database_name, [TYPE], MAX(backup_finish_date) AS backup_finish_date
FROM msdb..backupset
GROUP BY database_name, [TYPE]

quote:

---

Originally posted by Lumbago

quote:

---

Originally posted by RickD
I am not sure where the information on the backups would come from though as this information is held in the job itself or the maintenence plan if you are using them.

---

Backup information is in the msdb-database and the following query will give you the latest backups for all databases on a server:

SELECT database_name, [TYPE], MAX(backup_finish_date) AS backup_finish_date
FROM msdb..backupset
GROUP BY database_name, [TYPE]

--
Lumbago

---

SELECT database_name, [TYPE], physical_device_name, MAX(backup_finish_date) AS backup_finish_date
FROM msdb..backupset a
  INNER JOIN msdb..backupmediafamily b
    ON a.media_set_id = b.media_set_id
GROUP BY database_name, [TYPE], physical_device_name