Skip to content

Instantly share code, notes, and snippets.

Avatar
🕵️‍♂️
Figuring it out

Eitan Blumin EitanBlumin

🕵️‍♂️
Figuring it out
View GitHub Profile
@EitanBlumin
EitanBlumin / extended properties change tracking wrapper functions.sql
Last active Jul 24, 2021
Change Tracking Utility function and procedure to maintain last copied version
View extended properties change tracking wrapper functions.sql
CREATE FUNCTION dbo.ct_get_last_copied_version
(
@table_name sysname,
@target_identifier sysname = null -- optional parameter if you have multiple sync targets
)
RETURNS bigint
AS
BEGIN
RETURN ISNULL((SELECT convert(bigint, [value])
FROM sys.extended_properties
@EitanBlumin
EitanBlumin / extended properties global variable wrapper functions.sql
Created Jul 24, 2021
Function and stored procedure to implement Global Variables using Extended Properties
View extended properties global variable wrapper functions.sql
-- Function to Retrieve a global variable value
-- don't forget to convert to the correct data type
CREATE FUNCTION dbo.global_variable(@VariableName sysname)
RETURNS sql_variant
AS
BEGIN
RETURN (SELECT [value]
FROM sys.extended_properties
WHERE major_id = 0 AND minor_id = 0
AND [name] = @VariableName)
View Asynchronous Ledger Demo.sql
/*
Asynchronous Ledger Demo
========================
Author: Eitan Blumin | https://eitanblumin.com | https://madeiradata.com
Date: 2021-07-03
Description:
This script demonstrates a use case of a high-throughput table
which serves as a "hot-spot" for inserts and updates and queries.
This causes performance problems due to long lock chains, possible deadlocks,
and sometimes even worker thread starvation.
@EitanBlumin
EitanBlumin / Extended Properties as Database Global Variables.sql
Created Jun 14, 2021
Use Extended Properties as Database Global Variables
View Extended Properties as Database Global Variables.sql
/*
================================================
Extended Properties as Database Global Variables
================================================
Author: Eitan Blumin | https://madeiradata.com | https://eitanblumin.com
Date: 2021-06-04
Description:
Use this sample script as a template or starting point
for when you want to utilize extended properties
to save and retrieve values as if using "global" variables
@EitanBlumin
EitanBlumin / sp_help_revlogin2.sql
Last active May 5, 2021
sp_help_revlogin2 is a simpler alternative to sp_help_revlogin
View sp_help_revlogin2.sql
IF OBJECT_ID('tempdb..#sp_help_revlogin2') IS NOT NULL DROP PROCEDURE #sp_help_revlogin2
GO
/*********************************************************************************************
sp_help_revlogin2 V1.0
Eitan Blumin
https://eitanblumin.com | https://madeiradata.com
https://gist.github.com/EitanBlumin/1f19b0b3f59a9220641c559653b90f15
https://github.com/MadeiraData/MadeiraToolbox/blob/master/Utility%20Scripts/sp_help_revlogin2.sql
View sp_DBPermissions.sql
IF OBJECT_ID('tempdb..#sp_DBPermissions') IS NOT NULL DROP PROCEDURE #sp_DBPermissions
GO
/*********************************************************************************************
sp_DBPermissions V6.1
Kenneth Fisher
http://www.sqlstudies.com
https://github.com/sqlstudent144/SQL-Server-Scripts/blob/master/sp_DBPermissions.sql
This stored procedure returns 3 data sets. The first dataset is the list of database
View sp_SrvPermissions.sql
IF OBJECT_ID('#sp_SrvPermissions') IS NOT NULL DROP PROCEDURE #sp_SrvPermissions
GO
/*********************************************************************************************
sp_SrvPermissions V6.1
Kenneth Fisher
http://www.sqlstudies.com
https://github.com/sqlstudent144/SQL-Server-Scripts/blob/master/sp_SrvPermissions.sql
This stored procedure returns 3 data sets. The first dataset is the list of server
@EitanBlumin
EitanBlumin / simplest_alternative_to_sp_MSforeachdb.sql
Created Feb 17, 2021
The simplest possible alternative to sp_MSforeachdb
View simplest_alternative_to_sp_MSforeachdb.sql
/*
Author: Eitan Blumin | https://eitanblumin.com | https://madeiradata.com
Description:
This is the simplest possible alternative to sp_MSforeachdb which is not too great.
Instructions:
1. Replace the contents of the @CMD variable with the command you want to run inside each database.
2. The command will be run within the context of each online database in the SQL Server instance.
Remarks:
@EitanBlumin
EitanBlumin / Configure load balancer IP and name for a domain-independent AG.ps1
Created Jan 20, 2021
Script to configure load balancer listener IP and name for a domain-independent Availability Group in a Windows Workgroup
View Configure load balancer IP and name for a domain-independent AG.ps1
<#
.SYNOPSIS
Script to configure load balancer listener IP and name for a domain-independent Availability Group in a Windows Workgroup
Author: Eitan Blumin
Date: 2021-01-20
.DESCRIPTION
This script is adapted from the scripts provided in the following resources:
View Relocate AG Database Files.ps1
############## Setup ##############
$AGName = ''
$AGPrimary = ''
$AGSecondary = ''
# This allows you to process just a subset of databases using the name (wildcards are possible)
$DBNamePattern = 'AdventureW*'
$TargetFolder = 'K:\Data\'