Using SQL Server to view all alerts in SharePoint

Here is a query to see all the alerts that are in SharePoint. This query works well in Microsoft Office SharePoint Server 2007 (MOSS 2007) and Windows SharePoint Services (WSS 3.0). If you use it for Windows SharePoint Services 2.0 or SharePoint Portal Server 2003, you will need to remove the alertTitle column since it does not exist in the older database schema.

select * from 
(-- Immediate
select 'Instant' as NotifyFreq, alertTitle, listTitle, tp_email, tp_login, s.siteurl + weburl + '/_layouts/sitesubs.aspx' as url from dbo.ImmedSubscriptions s join userinfo u on (s.userid = u.tp_id)
union
-- Scheduled 
select case NotifyFreq when 1 then 'Daily' when 2 then 'Weekly' else 'Other' end , alertTitle, listTitle, tp_email, tp_login, s.siteurl + weburl + '/_layouts/sitesubs.aspx' as url from dbo.SchedSubscriptions s join userinfo u on (s.userid = u.tp_id) 
) t

The first column indicates the type of alert. The second column is what you see on the admin page for alerts. The url and tp_login columns are very useful if you want to delete a particular alert.

The url is actual the url to the alert edit page in SharePoint. You can remove ‘/_layouts/sitesubs.aspx’ from the url if you want to see the url to the list that the alert is associated with. Once you are one the alerts admin page, you can will need the tp_login to find the alert is the drop down list.