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.

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.