matthew-n

These are my red gate snippets. 

The powershell file "PushSnippets.ps1" is used to update them. 
It can be modified so as to push your own snippets once you create a GIST for them.

matthew-n

Processing JSON using jq

jq is useful to slice, filter, map and transform structured json data.

Installing jq

On Mac OS

brew install jq

matthew-n

--SELECT * FROM sys.databases

SELECT 'SERVER OPTIONS' AS 'container' 

/*
	Author: Tim Cartwright
	Purpose: Allows you to check the server, and client SET options

	https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-user-options-server-configuration-option
	1		DISABLE_DEF_CNST_CHK	Controls interim or deferred constraint checking.

matthew-n

IF OBJECT_ID('dbo.ErrorHandler') IS NULL BEGIN 
  EXEC ('CREATE PROCEDURE dbo.ErrorHandler AS BEGIN SELECT 1; END;');
END
GO

--our error handling procedure
ALTER PROCEDURE dbo.ErrorHandler @procName NVARCHAR(128), @ErrorMessage NVARCHAR(4000) OUTPUT, @ErrorSeverity INT OUTPUT, @ErrorState INT OUTPUT
AS
BEGIN
  /* declare sessions keys that are safe for multiple sessions on a single connection */

matthew-n

Setting up for minimal user (non-dba) permissions

One-time steps

USE master;
GO

CREATE CERTIFICATE diagnostics_cert
 ENCRYPTION BY PASSWORD = '5OClockSomewhere'

matthew-n

CREATE TABLE ##sample_set (
	sample_set_id INT NOT NULL IDENTITY(1,1),
	[database_id] INT NOT NULL,
	[database_name] NVARCHAR(128) NOT NULL,
	[table_id] INT NOT NULL,
	[table_name] NVARCHAR(128) NOT NULL,
	[column_id] INT NOT NULL,
	[column_name] NVARCHAR(128) NOT NULL,
	[stats_id] INT NULL,
	[status_name] NVARCHAR(128) NULL,

matthew-n

/* original code from: http://davebland.com/a-faster-alternative-to-sp_helpdb */

DECLARE @t1 DATETIME;
DECLARE @t2 DATETIME;

SET @t1 = GETDATE();

CREATE TABLE #DBSize
(
    DatabaseName VARCHAR(200),

matthew-n

CREATE FUNCTION dbo.udf_ipv4_format (@addr binary(16) )
RETURNS varchar(20) with SCHEMABINDING AS
BEGIN 
return IIF(SUBSTRING(@addr,0,8)=0,
			CONCAT( 
				CAST(SUBSTRING(@addr,13,1) AS INT),'.',
				CAST(SUBSTRING(@addr,14,1) AS INT),'.',
				CAST(SUBSTRING(@addr,15,1) AS INT),'.',
				CAST(SUBSTRING(@addr,16,1) AS INT)
			),NULL)

matthew-n

DROP TABLE IF EXISTS pgbench_generic_log;
CREATE TABLE pgbench_generic_log (
  mtime timestamptz not null default now(),
  action char not null check (action in ('I', 'U', 'D')),
  username text not null,
  table_name text not null,
  row_data jsonb not null
);

CREATE INDEX ON pgbench_generic_log USING brin (mtime);

matthew-n

# import config.
# You can change the default config with `make cnf="config_special.env" build`
cnf ?= config.env
include $(cnf)
export $(shell sed 's/=.*//' $(cnf))

# import deploy config
# You can change the default deploy config with `make cnf="deploy_special.env" release`
dpl ?= deploy.env
include $(dpl)