tcartwright

class JDBCConnectionString {
    [string]$subprotocol
    [string]$subname
    [string]$host
    [int]$port

    JDBCConnectionString([string]$connectionString) {
        $parts = $connectionString.Split(";")
        $protocols = $parts[0].Split(":")
        $this.subprotocol = $protocols[1]

tcartwright

[CmdletBinding()]
param (
    [Parameter(Mandatory=$true)]
    [string[]]$servers,
    [Parameter()]
    [string]$dbName = "master",
    [Parameter()]
    [string]$InstallScriptFileName = "Install-All-Scripts.sql"
)

tcartwright

#Requires -RunAsAdministrator
Clear-Host

#controls whether or not the alias keys are deleted before adding first
$cleanKeys = $true

#backup in case the requires does not work
$principal = New-Object Security.Principal.WindowsPrincipal([Security.Principal.WindowsIdentity]::GetCurrent())
if (!($principal.IsInRole([Security.Principal.WindowsBuiltInRole]::Administrator))) {
    throw "Administrator rights required"

tcartwright

/*
	Authors: Matthew Naul, Tim Cartwright
	Purpose: to build a stack trace so that nested stored proces can be followed in the case of an exception.
	Versions: Tested against 2008+
	NOTES: The context functions in use for the 2016 version of the stored procedure are not available in lower versions, 
		So a different version of the proc is applied that does string manipulation to perform nearly identical stack traces.
*/
USE [master]
GO 

tcartwright

USE [master]

IF OBJECT_ID('dbo.sp_session_set') IS NULL BEGIN
	EXEC ('CREATE PROCEDURE dbo.sp_session_set AS BEGIN SELECT 1; END;');
END
IF OBJECT_ID('dbo.fn_session_get') IS NULL BEGIN
	EXEC ('CREATE FUNCTION dbo.fn_session_get (@key sysname) RETURNS SQL_VARIANT AS BEGIN RETURN 1; END;');
END

GO

tcartwright

# list installed RSAT tools
Get-WindowsCapability -Name RSAT* -Online | Select-Object -Property DisplayName, State

# install ALL RSAT tools
Get-WindowsCapability -Name RSAT* -Online | ForEach-Object { Add-WindowsCapability -Online -Name "$($_.DisplayName)" }

# install a specific RSAT tool
Add-WindowsCapability -Online -Name "DISPLAY NAME HERE"

tcartwright

[CmdletBinding()]
param (
    [Parameter(Mandatory=$true)]
    [string]$server,
    [Parameter(Mandatory=$true)]
    [string]$dbName
)


Clear-Host

tcartwright

IF OBJECT_ID('dbo.fn_dependency_hierarchy') IS NOT NULL BEGIN 
	DROP FUNCTION dbo.fn_dependency_hierarchy 
END
GO

CREATE FUNCTION dbo.fn_dependency_hierarchy (@rootObjectId INT, @objectId int) RETURNS XML
BEGIN RETURN (
	SELECT CONCAT(QUOTENAME(OBJECT_SCHEMA_NAME (sed.referencing_id)), '.',  QUOTENAME(OBJECT_NAME(sed.referencing_id))) AS [@name], 
		o.type_desc AS [@type],
		dbo.fn_dependency_hierarchy (@rootObjectId, sed.referencing_id) AS [Dependencies]

tcartwright

Converted into a module: https://github.com/tcartwright/tcdbtools

PS Gallery link: https://www.powershellgallery.com/packages/tcdbtools/

tcartwright

SET NOEXEC OFF
GO

-- Uncomment the :SETVAR lines out if running from SSMS, and enable SQLCMD mode
-- :SETVAR __UnInstall "0"
GO

/*
	Original: https://www.sqlserver-dba.com/2020/03/extended-events-capture-query-errors.html