Tag Archives: TSQL

VIDEO – Sync tables with easy TSQL Stored Procedure

I needed to merge two tables of data by matching primary key and then populating all other columns.   The below TSQL code will accomplish that quickly and easily native within SQL.   No need for remote client side PowerShell Dot Net.   Hope you find this helpful.  Cheers!  shades_smile

Video

Code

CREATE PROCEDURE [dbo].[Sync] AS 
--Insert new rows
PRINT 'INSERT'
INSERT INTO [States]
([Name])
SELECT 
[Name]
FROM [States-Temp]
WHERE [Name] NOT IN (SELECT [Name] FROM [States])
--Delete excess rows
PRINT 'DELETE'
DELETE FROM [States] 
WHERE [Name] NOT IN (SELECT [Name] FROM [States-Temp])
--Update matching rows
PRINT 'UPDATE'
UPDATE t2 
SET t2.Abbr = t1.Abbr,
t2.Population = t1.Population,
t2.Capital = t1.Capital
FROM [States] AS t2
INNER JOIN [States-Temp] AS t1 
ON t2.[Name]=t1.[Name]
GO

Screenshots

image

image

image

image

Reference

Measure SQL tables with ROWCOUNTS and STORAGE

With custom SQL line of business databases it can be helpful to enumerate all tables and row counts for each.   The below TSQL will create a new VIEW named “RowCounts” for that purpose.   I’d suggest adding this to any custom SQL database for easy data quality checks and monitoring.

Cheers!   shades_smile

TSQL RowCount

CREATE VIEW RowCounts AS (
SELECT o.NAME,
  i.rowcnt 
FROM sysindexes AS i
INNER JOIN sysobjects AS o
ON i.id = o.id
WHERE i.indid < 2 AND OBJECTPROPERTY(o.id, 'IsMSShipped') = 0)

TSQL Storage

SELECT 
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB, 
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
SUM(a.used_pages) * 8 AS UsedSpaceKB, 
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM 
sys.tables t
INNER JOIN      
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
sys.schemas s ON t.schema_id = s.schema_id
WHERE 
t.NAME NOT LIKE 'dt%' 
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255 
GROUP BY 
t.Name, s.Name, p.Rows
ORDER BY 
t.Name

Screenshot

image

References

TSQL – Set all databases SIMPLE recovery

Use the below TSQL script to set all databases to simple recovery mode.   Helpful for development environments and lab VMs to save on disk storage and backup space.

Cheers!  shades_smile

Code

USE MASTER
declare
@isql varchar(2000),
@dbname varchar(64)
declare c1 cursor for select name from master..sysdatabases where name not in ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB')
open c1
fetch next from c1 into @dbname
While @@fetch_status <> -1
    begin
	
    select @isql = 'ALTER DATABASE @dbname SET RECOVERY SIMPLE'
    select @isql = replace(@isql,'@dbname',@dbname)
    print @isql
    exec(@isql)
    select @isql='USE @dbname; DBCC SHRINKFILE (N''@dbname_log'' , 0, TRUNCATEONLY)'
    select @isql = replace(@isql,'@dbname',@dbname)
    print @isql
    exec(@isql)
    fetch next from c1 into @dbname
    end
close c1
deallocate c1

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
Return to Top ▲Return to Top ▲