Ever notice that there is not way to centrally manage or get information about email aliases? Oh that annoys me! So I did what any self respecting geek would do, and I started taking it apart… Here are a couple SQL utility scripts to help you get more out of your SharePoint! Standard Disclaimer – Don't mess around with the DB… You can break stuff! Now, here's the goods:
– SQL Script to find out where an Email Alias is in use
– Written by Josef Nielsen, 2008
– nielsenjl_at_ldschurch.org
– /echef
– Run this script against the Farm Configuration DB
SELECT [Alias], [Deleted], [SiteMap].[Path], [Objects].[Name], [ListId]
FROM [WSS_Farm_Config].[dbo].[EmailEnabledLists]
INNER JOIN [SiteMap] ON [EmailEnabledLists].[SiteId] = [SiteMap].[Id]
INNER JOIN [Objects] ON [SiteMap].[DatabaseId] = [Objects].[Id]
– Change this value to the Alias you are looking for
WHERE [Alias] = 'MyEmailAlias' – This is the Name part of Name@SharePoint.server.com
– Run this script against the Content DB that the Site resides on
SELECT [tp_Title], [tp_ServerTemplate], [tp_ItemCount], [tp_Description], [tp_EmailInsertsFolder], [tp_EmailAlias], [tp_Fields], [tp_ContentTypes], [tp_DefaultWorkflowId]
FROM [WSS_ContentDB_01].[dbo].[AllLists]
–Replace the GUID Below with the GUId of the List you are looking for
WHERE [tp_Id] = 'A4BB3401-3161-430A-B330-42143C3DE879'
So, what do you get out of this? Well, the first script, run against your Config DB will give you the managed path and site collection name where that alias is in use. It will also tell you which content DB contains that site collection. It also gives you a GUID for the List.
In the second script, add the GUID you got for the list and run it against the Content DB that was also specified by the first script. This will give you the friendly name of the list that uses this alias, as well as a few other yummy bits of data about this list.
Enjoy!
josef.nielsen