image
SPJeff
Uncategorized

SMAT – Content Database Query

While reviewing the SharePoint Migration Assessment Tool (SMAT) I noticed the high performance data gathering. Running on a farm with thousands of sites the collection of data ran quickly.

Looking more closely we can see how Content Database SQL queries are used to collect a list of compatibility issues and populate the report. These are much faster than Server Object Model (SOM) front end techniques for collecting data.

Below are sample SQL queries that SMAT uses to check SharePoint content database and identify customizations that might not be compatible with Office 365.

Cheers!

shades_smile

SMAT Download

SMAT Report Categories

Microsoft.SharePoint.Migration.Scan.Scanner

image

AlertsScanner

SELECT Distinct(WebId), SiteId 
FROM [dbo].[ImmedSubscriptions] with (nolock) 
INNER JOIN [dbo].[AllSites] with (nolock) 
ON AllSites.Id = ImmedSubscriptions.SiteId 
WHERE AllSites.BitFlags & 1 = 0 AND AllSites.BitFlags & 2 = 0 
Union 
SELECT Distinct(WebId), SiteId 
FROM [dbo].[SchedSubscriptions] with (nolock) 
INNER JOIN [dbo].[AllSites] with (nolock) 
ON AllSites.Id = SchedSubscriptions.SiteId 
WHERE AllSites.BitFlags & 1 = 0 AND AllSites.BitFlags & 2 = 0 
ORDER BY SiteId

AppsScanner

SELECT Distinct UserInfo.tp_Login AS OwnerLogin,  
UserInfo.tp_Title AS OwnerTitle,  
AppPackages.Title AS AppTitle,  
CASE      WHEN AppSourceInfo.AppSource = 1
           THEN 'Marketplace'
		   WHEN AppSourceInfo.AppSource = 2
           THEN 'CorporateCatalog'
		   WHEN AppSourceInfo.AppSource = 3
		   THEN 'DeveloperSite'
		   WHEN AppSourceInfo.AppSource = 4
		   THEN 'ObjectModel'
		   WHEN AppSourceInfo.AppSource = 5
		   THEN 'RemoteObjectModel'  
END AS AppSource, 
AppPackages.SiteId, 
AppInstallations.Id AS AppId, 
AppInstallations.WebId, 
AppInstallations.LaunchUrl, 
AppInstallations.CreationTime, 
AppInstallations.RemoteAppUrl, 
AppInstallations.SettingsPageUrl, 
Webs.Title AS WebSiteTitle, 
Webs.FullUrl AS WebURL, 
CASE WHEN (LEN(Docs.DirName) > 0 AND LEN(Docs.LeafName) > 0)
       THEN Docs.DirName+'/'+ Docs.LeafName
	   WHEN (LEN(Docs.DirName) = 0 AND LEN(Docs.LeafName) > 0)
	   THEN '/' + Docs.LeafName
	   ELSE 'No App Part Placed'  
END AS PageURL 
FROM  dbo.AppPackages WITH(NOLOCK) 
INNER JOIN dbo.AppInstallations WITH(NOLOCK)
      ON AppPackages.SiteId = AppInstallations.SiteId 
INNER JOIN dbo.AppSourceInfo WITH (NOLOCK)
      ON AppPackages.PackageFingerprint = AppSourceInfo.PackageFingerprint
	  AND AppInstallations.SourceInfoId = AppSourceInfo.SourceInfoId
	  AND AppInstallations.SiteId = AppSourceInfo.SiteId 
INNER JOIN dbo.Sites WITH (NOLOCK)
      ON AppInstallations.SiteId = Sites.Id 
INNER JOIN dbo.Webs WITH (NOLOCK)
      ON Webs.Id = AppInstallations.WebId 
INNER JOIN dbo.UserInfo WITH (NOLOCK)
      ON UserInfo.tp_SiteID= Sites.Id
	  AND UserInfo.tp_ID = Sites.OwnerID 
Left Outer Join AllWebParts With (NoLock)
      ON AllWebParts.tp_SolutionId = AppPackages.ProductId
	  AND AllWebParts.tp_SiteId = AppPackages.SiteId 
Left Outer Join Docs WITH (NoLock)
      ON Docs.Id = AllWebParts.tp_PageUrlID
	  AND Docs.SiteId = AppInstallations.SiteId
	  AND Docs.WebId = AppInstallations.WebId 
WHERE (UserInfo.tp_SiteAdmin = 1
      AND Sites.Deleted= 0
      AND Webs.DeleteTransactionId= 0x)
      AND Sites.BitFlags & 1 = 0
	  AND Sites.BitFlags & 2 = 0

BrowserFileHandlingScanner

SELECT AD.SiteId, AD.DirName + N'/' + AD.LeafName as FilePath, AD.TimeCreated, AD.TimeLastModified, (UI.tp_Title + '['+UI.tp_Login+']') as Editor 
FROM AllDocs AS AD with (nolock) 
INNER JOIN AllSites as Sites with (nolock)
      ON Sites.Id = AD.SiteId 
INNER JOIN AllUserData as AUD with (nolock)
      ON (AD.DocLibRowID = AUD.tp_ID
      and Sites.Id = AUD.tp_SiteID      and AD.ListID = AUD.tp_ListId) 
INNER JOIN UserInfo as UI with (nolock)
      ON (AUD.tp_Editor = UI.tp_ID
      AND Sites.Id = UI.tp_SiteID)	  
WHERE (AD.LeafName LIKE '%.htm' or AD.LeafName LIKE '%.html')
      AND AD.SetupPath IS NULL
      AND AD.ListId IS NOT NULL
      AND AD.IsCurrentVersion = 1
      AND LEN(AD.DeleteTransactionID) <= 0
      AND Sites.BitFlags & 1 = 0
      AND Sites.BitFlags & 2 = 0
      AND AUD.tp_IsCurrent = 1 
ORDER BY SiteId

CheckedOutFilesScanner

SELECT SiteId, '/' + DirName + '/' + LeafName as FilePath 
FROM [dbo].[AllDocs] AS Docs with (NOLOCK) 
INNER JOIN [dbo].[AllSites] with (NOLOCK) 
ON AllSites.Id = Docs.SiteId 
WHERE CheckoutUserId is not null AND CheckoutExpires is null AND AllSites.BitFlags & 1 = 0 AND AllSites.BitFlags & 2 = 0 
GROUP BY SiteId, '/' + DirName + '/' + LeafName

CustomizedPagesScanner

 SELECT SiteId,
          CASE WHEN Len(DirName) = 0
		  THEN '/' + LeafName
		  ELSE '/' + DirName + '/' + LeafName
          END as FilePath 
FROM [dbo].[AllDocs] with (NoLock) 
INNER JOIN [dbo].[AllSites] with (nolock)
      ON AllSites.Id = AllDocs.SiteId 
WHERE DeleteTransactionId = 0x
      AND Level = 1
      AND SetupPath IS NOT NULL
      AND (DocFlags & 64) <> 0
      AND AllSites.BitFlags & 1 = 0
      AND AllSites.BitFlags & 2 = 0 
Group By SiteId, DirName, LeafName 
Order By SiteId, DirName, LeafName

CustomProfilePropertyMappingScanner

SELECT Count(PV.[PropertyID]) AS CountPropertyUsed, PL.PropertyName 
FROM {0}.[UserProfileValue] AS PV with(nolock) 
INNER JOIN {0}.[PropertyList] AS PL with(nolock)
      ON PV.PropertyID = PL.PropertyID 
GROUP BY PV.PropertyID, PropertyName
     SELECT PropList.PropertyName AS PropertyName,  
PropList.[Length] AS PropertyLength,  
PropMap.DCAttribute AS PropertyMapping                                                                 
FROM {1}.[ADImportPropertyMapping] AS PropMap WITH (NOLOCK) 
INNER JOIN {1}.[PropertyList] AS PropList WITH (NOLOCK) 
ON PropMap.PropertyID = PropList.PropertyID 
INNER JOIN [dbo].[ADImportDCMapping] AS DCMapping WITH (NOLOCK) 
ON DCMapping.DCId = propmap.DCId 
WHERE DCMapping.ConnectionName = '{0}' 
GROUP BY DCMapping.DCName, PropMap.DCAttribute, PropList.PropertyName, PropList.[Length]

EmailEnabledListsScanner

SELECT [tp_SiteId] as SiteID,  
[tp_WebId] as WebID, 
[tp_ID] as ListId 
FROM [AllLists] with (nolock) 
INNER JOIN [AllSites] with (nolock) 
ON AllLists.tp_SiteId = AllSites.Id 
WHERE LEN(tp_EmailAlias) > 0 
AND AllLists.tp_DeleteTransactionId = 0x 
AND AllSites.BitFlags & 1 = 0 
AND AllSites.BitFlags & 2 = 0 
GROUP BY tp_SiteId, tp_WebId, tp_ID

EmailEnabledListsScanner2010

SELECT [SiteId] as SiteID,  
[tp_WebId] as WebID, 
[tp_ID] as ListId 
FROM [AllLists] with (nolock) 
INNER JOIN AllWebs  with (nolock)
ON AllWebs.Id = alllists.tp_webid
INNER JOIN [AllSites] with (nolock) 
ON AllWebs.SiteId = AllSites.Id 
WHERE LEN(tp_EmailAlias) > 0 
AND AllLists.tp_DeleteTransactionId = 0x 
AND AllSites.BitFlags & 1 = 0 
AND AllSites.BitFlags & 2 = 0 
GROUP BY SiteId, tp_WebId, tp_ID

FileVersionsScanner

SELECT adv.SiteId, Count(adv.Id) as VersionCount, DirName + N'/' + LeafName as FilePath 
FROM [dbo].[AllDocVersions] as adv with (nolock) 
INNER JOIN [dbo].[AllDocs] as ad with (nolock) 
ON ad.Id = adv.Id 
INNER JOIN [dbo].[AllSites] with (nolock) 
ON AllSites.ID = ad.SiteId 
WHERE AllSites.BitFlags & 1 = 0  
AND AllSites.BitFlags & 2 = 0 
Group By adv.SiteId, adv.Id, DirName, LeafName 
Having Count(adv.Id) > {0}

InfoPathScanner

SELECT SiteId, WebId, DirName, LeafName, AllDocs.Id as DocId 
FROM AllDocs (nolock) 
INNER JOIN AllSites with (nolock) 
ON AllSites.Id = AllDocs.SiteId 
WHERE Extension = 'xsn' 
AND DeleteTransactionId = 0x 
AND HasStream = 1 
AND AllSites.BitFlags & 1 = 0 
AND AllSites.BitFlags & 2 = 0 
ORDER BY SiteId, WebId, DirName

IRMEnabledLibraryScanner

 SELECT lists.SiteId, lists.WebId, lists.Title as ListTitle 
FROM (SELECT tp_Flags & 0x8000000000 as IRMEnabled,
             tp_SiteId as SiteID,
             tp_WebId as WebID,
             tp_Title as Title
			 FROM [AllLists] with (nolock)) lists 
INNER JOIN [AllSites] with (nolock)
      ON lists.SiteId = AllSites.Id 
WHERE lists.IRMEnabled <> 0
      AND AllSites.BitFlags & 1 = 0
      AND AllSites.BitFlags & 2 = 0 
ORDER BY SiteId

LargeExcelFilesScanner

SELECT AD.SiteId, AD.DirName + N'/' + AD.LeafName as FilePath, AD.Size / (1024 * 1024) as FileSizeInMB 
FROM AllDocs AS AD with(nolock) 
INNER JOIN AllSites with (nolock)
      ON AllSites.Id = AD.SiteId 
WHERE AD.LeafName LIKE '%.xlsx'
      AND AD.Size / (1024 * 1024) > {0}
      AND BitFlags &1 = 0
      AND BitFlags &2 = 0
      AND AD.IsCurrentVersion = 1
      AND LEN(AD.DeleteTransactionID) <= 0 
Group By AD.SiteId, AD.DirName, AD.LeafName, AD.Size

LargeListsScanner

SELECT AllLists.tp_SiteId as SiteId, AllLists.tp_WebId as WebId, AllListsAux.ListId 
FROM [AllListsAux] with (nolock) 
INNER JOIN [AllLists] with (nolock)
      ON AllListsAux.ListID = AllLists.tp_ID 
INNER JOIN [AllSites] with (nolock)
      ON AllListsAux.SiteId = AllSites.Id 
WHERE AllListsAux.ItemCount > {0}
      AND AllLists.tp_DeleteTransactionId = 0x
      AND AllSites.BitFlags & 1 = 0
      AND AllSites.BitFlags & 2 = 0 
GROUP BY tp_SiteId, tp_WebId, ListId

LargeListsScanner2010

SELECT [AllWebs].SiteId as SiteId, AllLists.tp_WebId as WebId, AllListsAux.ListId 
FROM [AllListsAux] with (nolock)
INNER JOIN [AllLists] with (nolock)
ON AllListsAux.ListID = AllLists.tp_ID
INNER JOIN [AllWebs] with (nolock)
ON AllLists.tp_WebId = [AllWebs].Id
INNER JOIN [AllSites] with (nolock)
ON [AllWebs].SiteId = [AllSites].Id
WHERE AllListsAux.ItemCount > 5000
AND AllLists.tp_DeleteTransactionId = 0x
AND AllSites.BitFlags & 1 = 0
AND AllSites.BitFlags & 2 = 0 
GROUP BY SiteId, tp_WebId, ListId

LargeSitesScanner

SELECT [Id] as SiteId, [DiskUsed]/Power(1024,3) as SizeInGB 
FROM [dbo].[AllSites] with (nolock) 
Where [DiskUsed]/Power(1024,3) > {0}
      AND BitFlags & 1 = 0
      AND BitFlags & 2 = 0

LockedSitesScanner

SELECT Id  
FROM AllSites with (nolock) 
WHERE BitFlags & 1 > 0  
AND BitFlags & 2 > 0

LongODBUrlsScanner

DECLARE @Length256 AS int = 256-{0} 
DECLARE @Length260 AS int = 260-{0} 
DECLARE @Length270 AS int = 270-{0} 
 SELECT  AllDocs.SiteId,          DirName,
 LeafName                             
FROM [dbo].[AllDocs] with (NoLock) 
INNER JOIN [dbo].[AllSites] with (nolock)
          ON AllDocs.SiteId = AllSites.Id 
INNER JOIN [dbo].[AllWebs] with (nolock)
          ON AllSites.RootWebId = AllWebs.Id 
WHERE (LEN(DirName) > @Length256
          OR LEN([DirName] + N'/' + [LeafName]) > @Length260)
          AND AllSites.BitFlags & 1 = 0
          AND AllSites.BitFlags & 2 = 0
          AND AllWebs.WebTemplate = 21 
SELECT  NavNodes.SiteId,
          LEN(Url) AS UrlLength,
          Eid AS NodeId,
          WebId 
FROM [dbo].[NavNodes] with (NoLock) 
INNER JOIN [dbo].[AllSites] with (nolock)
          ON AllSites.Id = NavNodes.SiteID 
INNER JOIN [dbo].[AllWebs] with (nolock)
          ON AllSites.RootWebId = AllWebs.Id 
WHERE LEN(Url) > @Length260
          AND AllSites.BitFlags & 1 = 0
		  AND AllSites.BitFlags & 2 = 0
          AND AllWebs.WebTemplate = 21 
 SELECT  Perms.SiteId,
 LEN(ScopeUrl) AS ScopeUrlLength,
 ScopeUrl,
 WebId 
FROM [dbo].[Perms] with (NoLock) 
INNER JOIN [dbo].[AllSites] with (nolock)
          ON AllSites.Id = Perms.SiteID 
INNER JOIN [dbo].[AllWebs] with (nolock)
          ON AllSites.RootWebId = AllWebs.Id 
WHERE LEN(ScopeUrl) > @Length260
          AND AllSites.BitFlags & 1 = 0
		  AND AllSites.BitFlags & 2 = 0
          AND AllWebs.WebTemplate = 21 
 SELECT  Deps.SiteId,
 LEN(Deps.FullUrl) AS FullUrlLength,
 LEN(DepDesc) AS DepDescLength,
 Deps.FullUrl,
 DepDesc 
FROM [dbo].[Deps] with (NoLock) 
INNER JOIN [dbo].[AllSites] with (nolock)
       ON AllSites.Id = Deps.SiteID 
INNER JOIN [dbo].[AllWebs] with (nolock)
          ON AllSites.RootWebId = AllWebs.Id 
WHERE (LEN(Deps.FullUrl) > @Length260
          OR LEN(DepDesc) > @Length270)
          AND AllSites.BitFlags & 1 = 0
		  AND AllSites.BitFlags & 2 = 0
          AND AllWebs.WebTemplate = 21

NonDefaultMasterPageScanner

SELECT w.SiteID,
      w.ID as WebID,
      w.Title,
      w.MasterUrl,
      w.CustomMasterUrl 
FROM AllWebs w with (nolock) 
Inner JOIN AllSites s with (nolock)
      ON s.Id = w.SiteId 
WHERE       ((w.WebTemplate = 54 or w.WebTemplate = 21)
          and (w.MasterUrl not like '%_catalogs/masterpage/mysite15.master'
		  or w.CustomMasterUrl not like '%_catalogs/masterpage/mysite15.master'))
		  or      ((w.WebTemplate <> 54 and w.WebTemplate <> 21)
          and (w.MasterUrl not like '%_catalogs/masterpage/seattle.master'
		  or w.CustomMasterUrl not like '%_catalogs/masterpage/seattle.master'))
		  AND s.BitFlags & 1 = 0
		  AND s.BitFlags & 2 = 0

SandboxSolutionScanner

SELECT DISTINCT SOLUTIONS.SITEID, 
SOLUTIONS.SOLUTIONID, 
SOLUTIONS.NAME, 
SOLUTIONS.HASH, 
SOLUTIONS.HASASSEMBLIES, 
SOLUTIONS.STATUS, 
SITEINFO.SITECOLLECTIONURL, 
ALLDOCS.TIMECREATED, 
ALLDOCS.TIMELASTWRITTEN, 
USERINFO.TP_LOGIN AS 'USERLASTWRITTEN', 
CASE       WHEN SOLUTIONS.HASASSEMBLIES = 1 THEN 'CUSTOM CODE'
       WHEN SOLUTIONS.HASASSEMBLIES = 0 AND SOLUTIONFILES.SOLUTIONFILEPATH LIKE '%Onet.xml' THEN 'SITE TEMPLATE'
       ELSE 'DESIGN PACKAGE' 
END AS SOLUTIONTYPE 
FROM SOLUTIONS WITH (NOLOCK) 
INNER JOIN (SELECT DISTINCT SITES.ID AS SITEID,
              CASE
			  WHEN SITES.HOSTHEADER IS NOT NULL AND PARENTWEBS.FULLURL='' THEN SITES.HOSTHEADER
			  WHEN SITES.HOSTHEADER IS NOT NULL THEN SITES.HOSTHEADER + '/' + PARENTWEBS.FULLURL
			  ELSE '/' + PARENTWEBS.FULLURL END AS SITECOLLECTIONURL
			  FROM ALLWEBS AS CHILDWEBS WITH (NOLOCK)
			  INNER JOIN  ALLSITES AS SITES WITH (NOLOCK) ON CHILDWEBS.SITEID = SITES.ID
			  INNER JOIN  ALLWEBS AS PARENTWEBS WITH (NOLOCK) ON SITES.ROOTWEBID = PARENTWEBS.ID
			  WHERE SITES.DELETED = CONVERT(BIT, 0)
			  AND CHILDWEBS.DELETETRANSACTIONID = 0X
			  AND PARENTWEBS.DELETETRANSACTIONID = 0X
			  AND SITES.BITFLAGS & 1 = 0
			  AND SITES.BITFLAGS & 2 = 0
			  ) AS SITEINFO 
ON SITEINFO.SITEID=SOLUTIONS.SITEID AND SITEINFO.SITEID IS NOT NULL 
INNER JOIN ALLDOCS ALLDOCS WITH (NOLOCK) 
ON  ALLDOCS.SITEID = SOLUTIONS.SITEID 
AND ALLDOCS.DIRNAME = (CASE
      WHEN SITEINFO.SITECOLLECTIONURL = '/' THEN '_CATALOGS/SOLUTIONS'
      ELSE (SUBSTRING(SITEINFO.SITECOLLECTIONURL,2,LEN(SITEINFO.SITECOLLECTIONURL)-1)+ '/_CATALOGS/SOLUTIONS')
      END) 
AND ALLDOCS.LEAFNAME = SOLUTIONS.NAME 
INNER JOIN ALLUSERDATA ALLUSERDATA WITH (NOLOCK) 
ON  ALLUSERDATA.TP_SITEID = ALLDOCS.SITEID 
AND ALLUSERDATA.TP_LISTID = ALLDOCS.LISTID 
AND ALLUSERDATA.TP_ID = ALLDOCS.DOCLIBROWID 
INNER JOIN USERINFO USERINFO WITH (NOLOCK) 
ON  USERINFO.TP_SITEID = ALLUSERDATA.TP_SITEID 
AND USERINFO.TP_ID = ALLUSERDATA.TP_EDITOR    
LEFT OUTER JOIN SOLUTIONFILES WITH (NOLOCK) 
ON SOLUTIONFILES.SOLUTIONID = SOLUTIONS.SOLUTIONID 
AND SOLUTIONFILEPATH LIKE '%Onet.xml' 
WHERE SOLUTIONS.SOLUTIONGALLERYITEMID > 0 
ORDER BY SITEID

UnsupportedWebTemplatesScanner

SELECT [SiteId], Webs.[Id], Webs.[FullUrl], [Title], [WebTemplate] 
FROM [Webs] with (nolock) 
INNER JOIN [Sites] with (nolock)
      ON Sites.Id = Webs.SiteId 
WHERE Sites.BitFlags & 1 = 0
      AND Sites.BitFlags & 2 = 0
      AND WebTemplate NOT IN ({0}) 
ORDER BY SiteId

WorkflowRunning2010Scanner

SELECT WF.SiteId, WF.WebId, WF.ListId, WF.ItemID, WFA.Name as WorkflowName, UserInfo.tp_Login as WorkflowInitiator 
FROM [dbo].[Workflow] as WF with (nolock) 
Inner Join [dbo].[WorkflowAssociation] as WFA with (nolock)  
On WF.TemplateId = WFA.Id 
INNER JOIN[dbo].[AllSites] with(nolock) 
ON AllSites.Id = WFA.SiteId                     
Inner Join [dbo].UserInfo with (nolock) 
On WF.Author = UserInfo.tp_ID and WF.SiteId = UserInfo.tp_SiteID 
Where InternalState | 2 = 2 /*InternalState is only Running*/ 
AND AllSites.BitFlags & 1 = 0 
AND AllSites.BitFlags & 2 = 0

WorkflowRunning2013Scanner

 /*Gets all the webs that have a wfsvc list that indicates the site has 2013 workflows.*/ 
SELECT AllLists.tp_SiteID as SiteID, AllLists.tp_WebID as WebID 
FROM [AllLists] with (nolock) 
INNER JOIN [AllListsAux] with (nolock)
      ON AllLists.tp_ID = AllListsAux.ListID 
INNER JOIN [AllSites] with (nolock)
      ON AllSites.Id = AllLists.tp_SiteId 
WHERE AllLists.tp_Title = 'wfsvc'
       AND AllListsAux.ItemCount > 0
	   AND AllSites.BitFlags & 1 = 0
       AND AllSites.BitFlags & 2 = 0

Leave a Comment

Tags

Administration Angular App Model Azure Breeze Business Intelligence Central Admin CEWP Cloud Development DIAD InfoPath JavaScript JQuery JSON MOSS O365 Office Office365 Office 365 Performance Point PNP PowerBI PowerShell REST SharePoint SharePoint 2007 SharePoint 2010 SharePoint 2013 SharePoint 2016 SharePoint Administration SharePoint Designer SPFx SQL SSRS TSQL TypeScript Upgrade Video Virtualization Visual Studio 2010 VSCode WebAPI WITP Workflow

Recent Comments

Archives

website uptime
Return to Top ▲Return to Top ▲