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 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.