Skip to content

Instantly share code, notes, and snippets.

View guzmanda's full-sized avatar

Dan Guzman guzmanda

View GitHub Profile
@guzmanda
guzmanda / Replace-DatabaseNames.ps1
Last active August 8, 2022 16:38
Replace database name in T-SQL Scripts containing 3-part object name with specified database name
# 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.
@guzmanda
guzmanda / Test-SqlServerBrowser.ps1
Created February 16, 2019 12:26
Test SQL Server Browser
# #######################################################################
# ### 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
guzmanda / SystemHealthDeadlockReports.sql
Created June 24, 2019 21:21
Get xml_deadlock_report from system_health session file target
--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%'
)
# 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"
#