Useful SQL Queries For SharePoint Practitioners

In this article you will learn useful SQL Queries for SharePoint practitioners.

The SQL queries listed below can be very useful for SharePoint Farm administrators to manage/administer the SharePoint content/config database as well as the service application databases.

Read my previous article on some tools for SharePoint:
Note: The queries mentioned below are not specific only to SharePoint databases and can be used with any SQL database.
  1. To get the total number of space utilized by all the SharePoint databases in SQL server:
    1. SELECT CONVERT(DECIMAL(10,2),(SUM(size * 8.00) / 1024.00 / 1024.00)) As UsedSpace  
    2. FROM master.sys.master_files  
  2. To get the name of all the SharePoint databases in a SQL instance:
    1. Select * from Sys.Databases  
  3. To get the total number of space utilized by all the SharePoint databases in SQL server:
    1. SELECT CONVERT(DECIMAL(10,2),(SUM(size * 8.00) / 1024.00 / 1024.00)) As UsedSpace  
    2. FROM master.sys.master_files  
  4. To find the space used by a SharePoint DB and its free size:
    1. Replace MY_DB with the concerned database name  
    2. use "MY_DB"  
    3. exec sp_spaceused  
  5. To find the size consumed by SharePoint Databases individually in SQL Server:
    1. SELECT DB_NAME(database_id) AS DatabaseName,  
    2. Name AS Logical_Name,  
    3. Physical_Name, (size*8)/1024 SizeMB  
    4. FROM sys.master_files  
  6. To get the total number of SharePoint databases in the SQL server:
    1. select * from sys.databases  
    2. or  
    3. select COUNT(*) from sys.databases  
  7. To find the path for SQL Server error logs:
    1. o sp_readerrorlog  
  8. To get the total number of site collections in a Web application :
    1. select count(*) as 'Total Site Collection' from sites  
    Note: Point to the content database hosting that site collection and run this query

  9. To get the total number of sites in a web application :
    1. select count(*) from Webs  
    Note: Point to the content database hosting that site collection and run this query

  10. To get the Site Title and Site ID :
    1. select Title as 'Site title',FullUrl, SiteId as 'Site Collection Id' from Webs order by SiteId  
  11. To get the number of sites under each site collection in a web application :
    1. selec tSiteId, count(*) as 'Total Sub Sites' from Webs inner join Sites on Sites.Id = Webs.SiteId group by SiteId  
    Note: Point to the content database hosting that site collection and run this query.
Read more articles on SharePoint: