Executing the Same Query across all Databases

Eg:

Declare @sql varchar(1000)

set @Sql = 'select * from Contacts'

select 'use ' + name +
' ' + @sql from master.dbo.sysdatabases where dbid > 4


Copy the Statements to a new window and execute it.

Backup Report for the Server

Reports the ServerName , Database Name and LastBackupDate


select Convert(Varchar(25),server_name) as 'Server Name'
,convert(varchar(25),database_name) as 'Database'
,getdate() as reportdate, max(backup_finish_date) as 'Last_backup_date'
from msdb..backupset
Where database_name
in
(select name from master..sysdatabases)
and server_name = @@servername
group by server_name,database_name order by Last_backup_date DESC

Script for Changing the Job Owners across all the Jobs

Change the Owner of all the SQL Agent Jobs using the Following Script


SELECT 'EXEC MSDB.dbo.sp_update_job ' + char(13) +
'@job_name = ' + char(39) + j.[Name] + char(39) + ',' + char(13) +
'@owner_login_name = ' + char(39) + 'sa' + char(39) + char(13) + char(13)
FROM MSDB.dbo.sysjobs j
INNER JOIN Master.dbo.syslogins l
ON j.owner_sid = l.sid
WHERE l.[name] <> 'sa'
ORDER BY j.[name]