brovish’s gists

brovish / GeneratePartitionedView.sql

Created September 5, 2020 18:03 — forked from EitanBlumin/GeneratePartitionedView.sql

Stored procedure that creates a partitioned view on top of identically-named tables that exist in multiple databases

	/*
	Author: Eitan Blumin \| @EitanBlumin, https://www.eitanblumin.com
	Create Date: 2016-06-03
	Last Update: 2020-05-19
	Description:
	This procedure creates a partitioned view on top of identically-named tables that exist in multiple databases.

	Parameters:

	@DBNamePattern - Database name pattern to use for filtering the relevant databases

brovish / safe_UPSERT_pattern.sql

Created September 6, 2020 19:32

singleton/multi row insert/update examples

	BEGIN TRANSACTION;

	UPDATE dbo.t WITH (UPDLOCK, SERIALIZABLE) SET val = @val WHERE [key] = @key;

	IF @@ROWCOUNT = 0
	BEGIN
	INSERT dbo.t([key], val) VALUES(@key, @val);
	END

	COMMIT TRANSACTION;

brovish / [dbo].[gsp_generate_table_desc].sql

Created September 14, 2020 23:11

Generate sql string - create table with additional columns if necessary

	SET ANSI_NULLS ON
	SET QUOTED_IDENTIFIER ON
	GO

	CREATE PROCEDURE [dbo].[gsp_generate_table_desc]
	@tablename SYSNAME,
	@db_name SYSNAME,
	@create_with_identity BIT = 1,
	@table_prefix VARCHAR(20) = '',
	@Additional_attr VARCHAR(1000) = '',

brovish / soft_restart_sql_instance.sql

Last active March 11, 2021 10:51

Clears dmvs statistics, frees up memory used by SQL Server, ALTER INDEX ALL ON '+ @table + ' REBUILD

	use master
	DBCC SQLPERF('sys.dm_os_latch_stats',CLEAR);
	DBCC SQLPERF('sys.dm_os_wait_stats',CLEAR);
	CHECKPOINT
	DBCC DROPCLEANBUFFERS
	DBCC FREEPROCCACHE WITH NO_INFOMSGS;
	DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL

	USE CCR;
	GO

brovish / searchin_bad_objects.sql

Created October 9, 2020 11:38

	/*
	/////////////
	////ERROR////
	/////////////
	All error will be listed if object is broken
	*/
	DECLARE @AllObjectName TABLE (
	OrdinalNo INT IDENTITY
	,ObjectName NVARCHAR(MAX)
	,ObjectType NVARCHAR(MAX)

brovish / ntfs-filenames.txt

Created October 21, 2020 22:14 — forked from doctaphred/ntfs-filenames.txt

Invalid characters for Windows filenames

	Information from https://docs.microsoft.com/en-us/windows/win32/fileio/naming-a-file :

	Use any character in the current code page for a name, including Unicode
	characters and characters in the extended character set (128–255), except
	for the following:

	- The following reserved characters:

	< (less than)
	> (greater than)

brovish / Vagrant and Hyper-V Networking Configuration.md

Created November 28, 2020 00:52 — forked from savishy/Vagrant and Hyper-V Networking Configuration.md

Notes on Vagrant and Hyper-V

Vagrant and Hyper-V: Working with Virtual Network Interfaces

brovish / RSSFeeds.opml

Created November 28, 2020 16:03 — forked from bertwagner/RSSFeeds.opml

The OPML file of all of the SQL Server (and some other) RSS feeds I subscribe to as of 2019-07-01.

	<?xml version="1.0" encoding="UTF-8"?>

	<opml version="1.0">
	<head>
	<title>Bert's mostly SQL subscriptions in feedly Cloud</title>
	</head>
	<body>
	<outline text="Marketing" title="Marketing">
	<outline type="rss" text="Signal v. Noise" title="Signal v. Noise" xmlUrl="https://signalvnoise.com/posts.rss" htmlUrl="https://m.signalvnoise.com"/>
	<outline type="rss" text="Austin Kleon" title="Austin Kleon" xmlUrl="http://feeds2.feedburner.com/AustinKleon" htmlUrl="https://austinkleon.com"/>

brovish / xml_plan_read.sql

Created December 29, 2020 01:06

from book SQL Server Execution Plans Third Edition

	WITH Top1Query
	AS (SELECT TOP 1
	dest.text,
	deqp.query_plan
	FROM sys.dm_exec_query_stats AS deqs
	CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
	CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp
	ORDER BY deqs.execution_count DESC)
	SELECT TOP 3
	tq.text,

brovish / fn_XMLTable.sql

Created December 29, 2020 01:07

parse structure xml (from Stack Overflow)

	/************************************************************
	* Code formatted by SoftTree SQL Assistant © v11.0.35
	* Time: 12/29/2020 12:42:58 AM
	************************************************************/

	SET ANSI_NULLS ON
	GO
	SET QUOTED_IDENTIFIER ON
	GO
	CREATE FUNCTION [dbo].[XMLTable]