Whozits & Whatzit: Managing SQL Server email Accounts

/*
I don't know who or where I got this one.
this gives you all the email accounts listed on your SQL Server.
*/
CREATE TABLE #temp01
(
profile_id INT,
[name] VARCHAR(50),
description VARCHAR(50)
)
INSERT INTO #temp01
EXECUTE msdb.dbo.sysmail_help_profile_sp ;
CREATE TABLE #temp02
(
profile_id INT,
profile_name VARCHAR(50),
account_id INT,
account_name VARCHAR(50),
seq int
)
INSERT INTO #temp02
EXECUTE msdb.dbo.sysmail_help_profileaccount_sp ;
CREATE TABLE #temp03
(
account_id INT,
[name] VARCHAR(50),
description VARCHAR(50),
email_address VARCHAR(50),
display_name VARCHAR(50),
replyto_address VARCHAR(50),
servertype VARCHAR(50),
servername VARCHAR(50),
port INT,
username VARCHAR(50),
use_default_credentials VARCHAR(50),
enable_ssl int
)
INSERT INTO #temp03
EXECUTE msdb.dbo.sysmail_help_account_sp ;
SELECT a.name,
b.account_name,
c.description,
c.email_address,
c.display_name,
c.replyto_address,
c.servertype,
c.servername,
c.port,
c.username,
c.use_default_credentials,
c.enable_ssl
FROM [#temp01] AS a
INNER JOIN [#temp02] AS b ON a.profile_id = b.[profile_id]
INNER JOIN [#temp03] AS c ON b.account_id = c.account_id
DROP TABLE #temp01
DROP TABLE #temp02
DROP TABLE [#temp03]

whaa? Differential won't restore? (what bloke backed up last)

/*
We had to restore a full backup and a differential backup a while ago
To a local db to get some old data put back into the live database.
my manager & I had a heck of a time trying to find out where someone put the last back up.


This query helped us know a utility was still backing up to another location AFTER the job ran...
hence all the extents the DIFF were looking for were bye-bye into the rogue, full-backup.
*/

-------------copy below this line-----------------------

declare
@startDate DATETIME,
@endDate DATETIME,
@database sysname


-- enter your database and date range here:
select
@database = 'SSRC_V5SP1-2',
@startDate = '09/28/09',
@endDate = '09/30/09'


SELECT b.database_name,
b.backup_start_date,
b.backup_finish_date,
b.user_name,
f.logical_name,
f.physical_name,
mf.physical_device_name,
f.file_type,
f.file_size,
b.backup_size
FROM msdb.dbo.backupfile f,
msdb.dbo.backupset b,
msdb.dbo.backupmediafamily mf
WHERE f.backup_set_id = b.backup_set_id
AND b.media_set_id = mf.media_set_id
AND b.backup_start_date BETWEEN @startDate
AND @endDate
AND b.database_name = COALESCE
(@database,database_name)
ORDER BY b.database_name,
b.backup_start_date

SSRS: What reports do I have uploaded?

/*
this refers to a linked server called myServer
if you don't have a linked server remove the text "myServer."
*/
SELECT CASE C_1.TYPE
WHEN 2 THEN 'Report'
WHEN 3 THEN 'Resource'
WHEN 4 THEN 'Linked Report'
WHEN 5 THEN 'Data Source'
ELSE 'unknown'
END AS TypeOfReport,
C_1.ItemID,
C_1.Path,
C_1.Name,
C_1.ParentID,
C_1.Type,
C_1.[Content],
C_1.Intermediate,
C_1.SnapshotDataID,
C_1.LinkSourceID,
C_1.Property,
C_1.Description,
C_1.Hidden,
C_1.CreatedByID,
C_1.CreationDate,
C_1.ModifiedByID,
C_1.ModifiedDate,
C_1.MimeType,
C_1.SnapshotLimit,
C_1.Parameter,
C_1.PolicyID,
C_1.PolicyRoot,
C_1.ExecutionFlag,
C_1.ExecutionTime,
R.ItemID AS Expr1,
R.Name AS Expr2,
R.id,
R.primary_rs_reports_id,
R.ManagerViewOnly,
R.HasDollars,
R.add_date
FROM myServer.ReportServer.dbo.Catalog AS C_1
LEFT OUTER JOIN admin_primary_rs_reports AS R
ON C_1.ItemID=R.ItemID
WHERE (C_1.Path LIKE '/%')