guzmanda

# Wrapper script for T-SQL code analysis rules. 
# 
# 1) installs the latest Microsoft.SqlServer.DacFx.x64 NuGet package for the current user (if needed)
# 2) executes Add-Type for the Microsoft.SqlServer.TransactSql.ScriptDom.dll assembly for use by code analysis script
# 3) dot-sources code analysis PS function script
# 4) executes code analysis rules
#
# Example usage: 
#     powershell -ExecutionPolicy RemoteSigned -File "C:\PowerShellScripts\Execute-TSqlCodeAnalysis.ps1" -scriptFilePath = "C:\SqlScripts\YourScript.sql"
#

guzmanda

--get xml_deadlock_report from system_health session file target
WITH
	  --get full path to current system_health trace file
	  CurrentSystemHealthTraceFile AS (
		SELECT CAST(target_data AS xml).value('(/EventFileTarget/File/@name)[1]', 'varchar(255)') AS FileName
		FROM sys.dm_xe_session_targets
		WHERE
			target_name = 'event_file'
			AND CAST(target_data AS xml).value('(/EventFileTarget/File/@name)[1]', 'varchar(255)') LIKE '%\system[_]health%'
	)

guzmanda

# #######################################################################
# ### Test SQL Server Browser connection and display datagram results ###
# ### to help troubleshoot named instance connectivity problems.      ###
# #######################################################################

# verify UDP port 1433 connectivity and query SQL Server Browser for all instances
Function Get-SqlServerBrowerDatagramForAllInstances($hostNameOrIpAddress)
{

    Write-Host "Querying SQL Browser for all instances on host $hostNameOrIpAddress ..."

guzmanda

# Modify T-SQL scripts to replace database name in 3-part object names with specified value using T-SQL script DOM.

param (
    # Path to folder containing the scripts to modify. Files with .sql extension in this folder and subfolders will be processed.
    $scriptFolderPath = "C:\source\repos\DatabaseProjects\AdventureWorks2014",
    # Original database name to replace.
    $originalDatabaseName = "AdventureWorks2014",
    # New database name or SQLCMD variable name.
    $newDatabaseName = "[`$(DatabaseName)]",
    # Location of Microsoft.SqlServer.TransactSql.ScriptDom.dll assembly.