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 databases .

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:

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 :

select SiteId, 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

by Vignesh Ganesan via Everyone's Blog Posts - SharePoint Community