Skip to content

Instantly share code, notes, and snippets.

🕵
Figuring it out

Eitan Blumin EitanBlumin

🕵
Figuring it out
Block or report user

Report or block EitanBlumin

Hide content and notifications from this user.

Learn more about blocking users

Contact Support about this user’s behavior.

Learn more about reporting abuse

Report abuse
View GitHub Profile
@EitanBlumin
EitanBlumin / CompareInstanceProperties.sql
Last active Dec 23, 2018
Compare SQL Server Instance Properties
View CompareInstanceProperties.sql
----------------------------------------------------------------------------------
-- Created: by Eitan Blumin 26/06/18
-- Description:
-- Compares server level objects and definitions as outputted by the first script (GenerateInstancePropertiesForCompare.sql).
--
-- Instructions:
-- Run GenerateInstancePropertiesForCompare.sql on "First" server. Save output to a CSV file.
-- Run GenerateInstancePropertiesForCompare.sql on "Second" server. Save output to a CSV file.
-- Use this script ( CompareInstanceProperties.sql ) to load the files into a table, and output any differences
-- Don't forget to change file paths and server names accordingly.
@EitanBlumin
EitanBlumin / Find Orphaned Records By Foreign Key.sql
Last active Dec 17, 2018
Find which records are causing your Foreign Key check to fail
View Find Orphaned Records By Foreign Key.sql
/************** Find Orphaned Records **************
Author: Eitan Blumin
****************************************************/
DECLARE
@ForeignKeyName SYSNAME = 'FK_MyTable_MyOtherTable'
, @PrintOnly BIT = 0
DECLARE
@FKId INT,
@ChildTableID INT,
View FilterParseJsonParameters.sql
/*
Fully Parameterized Search Query
--------------------------------
Copyright Eitan Blumin (c) 2018; email: eitan@madeiradata.com
You may use the contents of this SQL script or parts of it, modified or otherwise
for any purpose that you wish (including commercial).
Under the single condition that you include in the script
this comment block unchanged, and the URL to the original source, which is:
http://www.eitanblumin.com/
@EitanBlumin
EitanBlumin / Get Recent SQL Error Log Errors.sql
Created Nov 22, 2018
Coalesces error messages from SQL Error Log into one row per exception
View Get Recent SQL Error Log Errors.sql
DECLARE @MinutesBackToCheck INT = 10;
SET NOCOUNT ON;
DECLARE @start DATETIME;
SET @start=DATEADD(MINUTE,-@MinutesBackToCheck,GETDATE());
DECLARE @errors AS TABLE
(
ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
LogDate DATETIME,
@EitanBlumin
EitanBlumin / ChangeJobStatusBasedOnHADR.sql
Last active Nov 14, 2018
This procedure detects whether the specified DB is primary or secondary in DB Mirroring or Availability Groups, and disables or enables a list of jobs accordingly. Run this from a job in both servers.
View ChangeJobStatusBasedOnHADR.sql
USE [SomeNonHadrDB]
GO
IF OBJECT_ID('ChangeJobStatusBasedOnHADR', 'P') IS NOT NULL DROP PROCEDURE ChangeJobStatusBasedOnHADR
GO
/*
--Sample usage:
EXEC ChangeJobStatusBasedOnHADR @DBName = 'DB_to_use_as_primary_indicator'
*/
CREATE PROCEDURE ChangeJobStatusBasedOnHADR
@DBName SYSNAME = NULL
View Data Driven Alternative Email and File Share.sql
-- Based on script by Jason Selburg
-- https://www.sqlservercentral.com/Forums/Topic279460-150-1.aspx
-- http://www.sqlservercentral.com/scripts/Miscellaneous/31733/
USE ReportServer
GO
IF OBJECT_ID(N'[dbo].[data_driven_subscription]', 'P') IS NOT NULL
DROP PROCEDURE [dbo].[data_driven_subscription]
GO
@EitanBlumin
EitanBlumin / check_untrusted_foreign_keys.sql
Last active Oct 14, 2018
Find and check untrusted Foreign Keys in all databases
View check_untrusted_foreign_keys.sql
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp;
CREATE TABLE #tmp (DBName SYSNAME, TableName SYSNAME, UntrustedObject NVARCHAR(1000));
EXEC sp_MSforeachdb '
INSERT INTO #tmp
SELECT ''?'', QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id, DB_ID(''?''))) + ''.'' + QUOTENAME(OBJECT_NAME(parent_object_id, DB_ID(''?''))), + QUOTENAME(name)
FROM [?].sys.foreign_keys
WHERE is_not_trusted = 1 AND is_not_for_replication = 0 AND is_disabled = 0'
SELECT DBName, TableName, UntrustedObject, CommandToRemediate = N'USE ' + QUOTENAME(DBName) + N'; ALTER TABLE ' + TableName + N' WITH CHECK CHECK CONSTRAINT ' + UntrustedObject
FROM #tmp
@EitanBlumin
EitanBlumin / Online Index Operations without Enterprise.sql
Last active Oct 8, 2018
Generate Script to allow performing ONLINE index operations and heavy changes on huge tables, without needing Enterprise edition of SQL Server
View Online Index Operations without Enterprise.sql
/***********************************************************************************
Copyright: Eitan Blumin (c) 2018
https://gist.github.com/EitanBlumin/79222fc2be5163cec828d0a69270a0ab
***********************************************************************************/
GO
IF OBJECT_ID('tempdb..#PrintMax', 'P') IS NOT NULL DROP PROCEDURE #PrintMax;
GO
-----------------------------------------------------------------------------------------------
@EitanBlumin
EitanBlumin / Generate Synonyms Creation Script.sql
Created Aug 21, 2018
Generate Synonyms Creation Script for creating a "shell" database
View Generate Synonyms Creation Script.sql
DECLARE
@LinkedServer NVARCHAR(300),
@DBInLinkedServer NVARCHAR(300)
SET @LinkedServer = '111.222.111.222'
SET @DBInLinkedServer = 'SomeOtherDatabase'
-- Generate create script for any non-system schemas:
SELECT CreateStatement = N'CREATE SCHEMA ' + QUOTENAME(name) + N';', DropStatement = N'DROP SCHEMA ' + QUOTENAME(name) + N';'
View Plan_To_Increase_Cost_Threshold_For_Parallelism.sql
DECLARE
@MinUseCount INT = 50 -- Set minimum usecount to ignore rarely-used plans
, @CurrentCostThreshold FLOAT = 5 -- Serves as minimum sub-tree cost
, @MaxSubTreeCost FLOAT = 30 -- Set the maximum sub-tree cost, plans with higher cost than this wouldn't normally interest us
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT @CurrentCostThreshold = CONVERT(FLOAT, value_in_use)
FROM sys.configurations
WHERE [name] = 'cost threshold for parallelism';
You can’t perform that action at this time.