This was a task from last week. There was a server where we wanted to make some adjustments to security including changing the database owner to SA. To do that we needed to make sure that we included all the Sharepoint databases. If you’ve ever looked at a SQL instance supporting Sharepoint it’s not one database – for the server I was working on there were more than 60! Some that were clearly Sharepont, some that you might guess, maybe some you wouldn’t guess.

We tried looking in the Sharepoint Config database, but we weren’t sure we were in the right place, so we did some searching and found a blog post that offered two methods, one using Powershell and one using the Sharepoint admin tools. We asked someone from the server team to try both, both worked, and both returned a list of 21 databases – not 60 (actually 63). Making the assumption that 21 was correct, we could go back to the Objects table and write this query (against a Sharepoint 2010 install):

SELECT

[Name], [Properties], convert(xml, properties).value(‘(//object/@type)[1]‘, ‘varchar(255)’)

FROM [SharePoint_Config].[dbo].[Objects]

where

properties like ‘%database,%’ and convert(xml, properties).value(‘(//object/@type)[1]‘, ‘varchar(255)’) like ‘%database%’

That returned the same list of 21 databases. We think the others are from old/trial/abandoned installs, so it goes on the list to take the others offline and see what breaks. All of them have the ‘ugly’ guid’s in the name – knowing where the db names are stored just makes me itch to try cleaning them up, but it’s not a big win and even suggesting that we touch Sharepoint sets off a ‘oh, that’s a bad idea’ vibe with everyone.

Perhaps a pause here is in order to contemplate an application that needs 21 databases. I’m guessing this is to support sharing the load across multiple servers if needed, but it also feels like it was partitioned by feature (and maybe that’s ok). Still, 21 databases?

It also turns out that Sharepoint doesn’t require the ugly db names, it just tilts that way if you do a default install. Here is post that shows how to set cleaner names. Fight the good fight when it’s time to install Sharepoint!