A few of the SQL Server 2005 helper scripts/maintenance scripts I've written recently have taken the form of
  • produce a SQL script
  • run it on all appropriate databases
  • display the output
To do this, I've adapted the following SQL to run the query I want, sometimes inserting data into a temporary table/table variable for later retrieval, and sometimes just selecting the results. The following example is checking each user database for a table called ErrorLog, and if there is one selecting the data from it, prefixing the results with a column identifying the server name (using @@SERVERNAME - this is because I've been running these scripts across many servers and manually concatenating the results) and the database name (using our local variable @DBName), and also printing any errors to the output window

DECLARE @Loop int
DECLARE @DBName varchar(300)
DECLARE @SQL varchar(max)
DECLARE @tableName VARCHAR(255)

SET @Loop = 1
SET @DBName = ''

   WHILE @Loop = 1
BEGIN

   SELECT TOP 1 @DBName = d.Name
   FROM master.sys.databases d
   WHERE d.Name > @DBName
   AND d.database_id not in (1, 2, 3, 4)
   ORDER BY d.Name

      SET @Loop = @@ROWCOUNT

   IF @Loop = 0
      BREAK

   SET @SQL =
      'IF EXISTS (SELECT 1 FROM [' + @DBName + '].sys.objects WHERE [name] = ''ErrorLog'')
            BEGIN
               SELECT ''' + @@SERVERNAME + ''', ''' + @DBName + ''', ErrorMessage, ErrorDate
               FROM [' + @DBName + '].dbo.ErrorLog
            END
            ELSE
            BEGIN
               PRINT ''ErrorLog table does not exist on server: ' + @@SERVERNAME + ' and database: ' + @DBName + '''
            END '

   BEGIN TRY
      EXEC master.dbo.sp_executeSQL @SQL
   END TRY
   BEGIN CATCH
      PRINT 'Error executing ' + @SQL + ' on server: ' + @@SERVERNAME + ' for database: ' + @DBName
   END CATCH
END