Finding List Of Database Used By A SharePoint Farm Using SQL Query In Configuration Database

There are times when we ignore the cleanup process of any database server which are used by various SharePoint Farms. When we remove a Farm without bothering about the database used, then it becomes very difficult to identify the databases used. At the same time, we do not have SP Farm with us to check, using Central Admin for PowerShell command.

Thus, if you came across this kind of scenario, don't worry as we have the solutions here. Using the query given below, we can find out the database names. 

Step 1

Run the query given below in Configuration database. 

===================== Query 1===============================

SELECT Id
,BaseClassId
,FullName
 FROM Classes
WHERE FullName LIKE 'Microsoft.SharePoint.Administration.SPDatabase,%'

========================================================

This will give a GUID.

Step 2

Now, using the ID, execute the query given below.

===================== Query 2===============================

DECLARE @returnValue int,
@RequestGuid uniqueidentifier

EXEC @returnValue = [dbo].[proc_getObjectsByBaseClass]
@BaseClassId = 'USE THE ID GOT IN ABOVE QUERY',
@ParentId = NULL,
@RequestGuid = @RequestGuid OUTPUT

SELECT @RequestGuid as N'@RequestGuid'
SELECT 'Return Value' = @returnValue

This will list out the ID (GUID) assigned for each database.

Step 3

Using one by one, you can get the all the database names in the column named 'Name'.

===================== Query 3===============================

DECLARE @returnValue int,
@RequestGuid uniqueidentifier

EXEC @returnValue = [dbo].[proc_getObject]
@Id = 'ID of Last Result',
@RequestGuid = @RequestGuid OUTPUT

SELECT @RequestGuid as N'@RequestGuid'
SELECT 'Return Value' = @returnValue

Now, you can go ahead and delete the database if you find it isn't useful anymore.

Note

To be on the safe side, please take a backup of the database.