SharePoint, Office 365, Azure, and Front end JS geek. – Chicago IL
SharePoint, Office 365, Azure, and Front end JS geek. – Chicago IL

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

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

TSQL Storage

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

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

AppsScanner

BrowserFileHandlingScanner

CheckedOutFilesScanner

CustomizedPagesScanner

CustomProfilePropertyMappingScanner

EmailEnabledListsScanner

FileVersionsScanner

InfoPathScanner

IRMEnabledLibraryScanner

LargeExcelFilesScanner

LargeListsScanner

LargeSitesScanner

LockedSitesScanner

LongODBUrlsScanner

NonDefaultMasterPageScanner

SandboxSolutionScanner

UnsupportedWebTemplatesScanner

WorkflowRunning2010Scanner

WorkflowRunning2013Scanner

© Copyright 2016
@ SPJeff

Return to Top ▲Return to Top ▲