Bill Baer /bɛːr/

The attached SQL scripts are intended as sample scripts and may require some modification to suit your individual environments; however, serve as a foundation and guidance in SQL scripting under Windows SharePoint Services 3.0.

Retrieve active Content Databases from a WSS 3.0 Configuration Database

• Returns a list of all active Content Databases in the Windows SharePoint Services Configuration Database.

USE <WSS_3.0_Configuration_Database>
SELECT DISTINCT SiteMap.DatabaseId, Objects.Name
FROM SiteMap
INNER JOIN Objects
ON SiteMap.DatabaseId=Objects.Id

Get Site Collections in Content Databases not in Configuration Database

• Returns all Site Collections in a Content Database using the Windows SharePoint Services 3.0 Configuration Database. Add additional Content Databases as required.

SELECT * FROM [<WSS_3.0_Configuration_Database>].dbo.SiteMap WHERE ApplicationID = ‘670181FD-E7AE-4230-A0B0-9261092D7A4C’ AND Id NOT IN (
(SELECT ID FROM[<SQL_Server_Instance>].<WSS_3.0_Content_Database>.dbo.Sites)
UNION (SELECT ID FROM [<SQL_Server_Instance>]. <WSS_3.0_Content_Database>.dbo.Sites)
)

Get Content Database and Database Server Information

• Returns Content Database and associated Database Server information.

SELECT SQLServers.Name AS SQLServerName, SQLInstances.Name AS SQLInstanceName, DBs.Name AS DBName
FROM Objects AS DBs
INNER JOIN Objects AS SQLInstances
ON DBs.ParentId = SQLInstances.Id
INNER JOIN Objects AS SQLServers
ON SQLInstances.ParentId = SQLServers.Id
INNER JOIN Classes
ON SQLInstances.ClassId = Classes.Id
WHERE Classes.Fullname LIKE ‘Microsoft.SharePoint.Administration.SPDatabaseServiceInstance%’

Get Site Collection Count from WSS 3.0 Content Database

• Returns the Site Collection count from a Windows SharePoint Services 3.0 Content Database.

USE <WSS_3.0_Configuration_Database>
SELECT * FROM Sitemap s
INNER JOIN Objects o
ON s.DatabaseId=o.Id
WHERE o.Name LIKE ‘<WSS_3.0_Content_Database>%’

Get Site Collection Mapping Information

• Maps a Windows SharePoint Services 3.0 Site Collection to its host Content Database using the Windows SharePoint Services 3.0 Configuration Database.

USE <WSS_3.0_Configuration_Database>
SELECT a.Path, a.Id, b.Name, c.Name
FROM <WSS_3.0_Configuration_Database>..SiteMap AS a INNER JOIN
<WSS_3.0_Configuration_Database>..Objects AS b ON a.DatabaseId = b.Id INNER JOIN
<WSS_3.0_Configuration_Database>..Objects AS c ON b.ParentId=c.Id
WHERE a.ApplicationId = ‘670181FD-E7AE-4230-A0B0-9261092D7A4C’

Compare WSS 2.0 and WSS 3.0 Content Databases [Useful in Gradual Upgrade Mode]

• Compares Windows SharePoint Services 2.0 and Windows SharePoint Services 3.0 Content Databases returning the variance.

USE [<WSS_3.0_Configuration_Database>]
GO
DECLARE @dbid UNIQUEIDENTIFIER
DECLARE @pairname NVARCHAR(100)
DECLARE @databasename NVARCHAR(100)
SELECT @pairname = ‘<WSS_3.0_Content_Database>’
SELECT @dbid = (SELECT [Id] FROM DBO.objects WHERE [Name] = @pairname)
SELECT REPLACE(t1.FullUrl,’http://<WSS_2.0_URL>’,’’)
FROM [<SQL_Server_Instance>].<WSS_2.0_Content_Database>.DBO.Sites t1
WHERE NOT EXISTS
(SELECT t2.[Path] FROM DBO.SiteMap t2 WHERE t2.DatabaseId = @dbid
AND t2.[Path] = REPLACE(t1.FullUrl,’http://<WSS_3.0_URL>’,’’))

Locate a specific Site Collection using the WSS 3.0 Configuration Database

• Locates a specific Site Collection in the Windows SharePoint Services 3.0 Configuration Database.

USE <WSS_3.0_Configuration_Database>
SELECT * FROM SiteMap s
JOIN Objects o ON s.DatabaseId = o.Id
AND s.Path LIKE ‘%<Site_Collection_Path>%’