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:

• Useful Tools For SharePoint Practitioners

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:
   

   SELECT CONVERT(DECIMAL(10,2),(SUM(size * 8.00) / 1024.00 / 1024.00)) As UsedSpace
   FROM master.sys.master_files

2. To get the name of all the SharePoint databases in a SQL instance:
   

   Select * from Sys.Databases

3. To get the total number of space utilized by all the SharePoint databases in SQL server:
   

   SELECT CONVERT(DECIMAL(10,2),(SUM(size * 8.00) / 1024.00 / 1024.00)) As UsedSpace
   FROM master.sys.master_files

4. To find the space used by a SharePoint DB and its free size:
   

   Replace MY_DB with the concerned database name
   use "MY_DB"
   exec sp_spaceused

5. To find the size consumed by SharePoint Databases individually in SQL Server:
   

   SELECT DB_NAME(database_id) AS DatabaseName,
   Name AS Logical_Name,
   Physical_Name, (size*8)/1024 SizeMB
   FROM sys.master_files

6. To get the total number of SharePoint databases in the SQL server:
   

   select * from sys.databases
   or
   select COUNT(*) from sys.databases

7. To find the path for SQL Server error logs:
   

   o sp_readerrorlog

8. To get the total number of site collections in a Web application :
   

   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 :
   

   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 :
    

    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 :
    

    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:

• SharePoint 2010: Customization Of The Data View Web Part - XSLT
• Playing Video In Restricted SharePoint Environments