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!

SMAT Download
SMAT Report Categories
Microsoft.SharePoint.Migration.Scan.Scanner

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