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

  1. SELECT Id  
  2. ,BaseClassId  
  3. ,FullName  
  4.  FROM Classes  
  5. WHERE FullName LIKE 'Microsoft.SharePoint.Administration.SPDatabase,%'  
========================================================

This will give a GUID.

Step 2

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

SQL

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

  1. DECLARE @returnValue int,  
  2. @RequestGuid uniqueidentifier  
  3.   
  4. EXEC @returnValue = [dbo].[proc_getObjectsByBaseClass]  
  5. @BaseClassId = 'USE THE ID GOT IN ABOVE QUERY',  
  6. @ParentId = NULL,  
  7. @RequestGuid = @RequestGuid OUTPUT  
  8.   
  9. SELECT @RequestGuid as N'@RequestGuid'  
  10. SELECT 'Return Value' = @returnValue  
This will list out the ID (GUID) assigned for each database.

SQL


Step 3


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

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

  1. DECLARE @returnValue int,  
  2. @RequestGuid uniqueidentifier  
  3.   
  4. EXEC @returnValue = [dbo].[proc_getObject]  
  5. @Id = 'ID of Last Result',  
  6. @RequestGuid = @RequestGuid OUTPUT  
  7.   
  8. SELECT @RequestGuid as N'@RequestGuid'  
  9. SELECT 'Return Value' = @returnValue  

SQL

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.

Ebook Download
View all
Learn
View all