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 / ultimate_compression_savings_estimation_whole_database.sql
Last active Feb 18, 2020
This ultimate script performs compression savings estimation check for an ENTIRE database, includes comparison between PAGE and ROW compression types, cautionary table size checks, AND it generates the remediation scripts for you!
View ultimate_compression_savings_estimation_whole_database.sql
----------------------------------------------------------------
-------- Ultimate Compression Savings Estimation Check ---------
----------------------------------------------------------------
-- Author: Eitan Blumin | https://www.eitanblumin.com
-- Create Date: 2019-12-08
-- Source: https://gist.github.com/EitanBlumin/85cf620f7267b234d677f9c3027fb7ce
----------------------------------------------------------------
-- Description:
-- ------------
-- This script performs compression savings estimation check for both PAGE and ROW
@EitanBlumin
EitanBlumin / helper_script_for_adding_schemabinding_to_scalar_functions.sql
Last active Feb 17, 2020
Helper T-SQL script for adding SCHEMABINDING on scalar functions (checks in ALL databases on the server)
View helper_script_for_adding_schemabinding_to_scalar_functions.sql
/*
Author: Eitan Blumin | https://www.eitanblumin.com
Description:
Helper T-SQL script for adding SCHEMABINDING on scalar functions (checks in ALL databases on the server)
Instructions:
1. Run the script to detect all scalar functions with disabled SCHEMABINDING, that can potentially have it enabled.
2. Review the 1st resultset for the full list of detected functions.
@EitanBlumin
EitanBlumin / TempDB Sizing Check and Remediation.sql
Created Jan 19, 2020
TempDB Sizing Check and Remediation script
View TempDB Sizing Check and Remediation.sql
/*
-----------------------------------
TempDB Sizing Check and Remediation
-----------------------------------
Author: Eitan Blumin | https://www.eitanblumin.com
Description:
This script makes sure that all TempDB files are equally sized, based on a calculation that takes into consideration
the disk volume where the TempDB files are located.
This check only works when TempDB files are isolated from other databases and exist on their own dedicated volume.
@EitanBlumin
EitanBlumin / Basic SQL Server table migration using Powershell.ps1
Created Feb 12, 2020
Basic SQL Server table migration using Powershell
View Basic SQL Server table migration using Powershell.ps1
# Author: Nathan Lifshes
# Date: 2020-01-13
Param (
[string] $SrcServer ,
[string] $SrcDatabase ,
[string] $DestServer ,
[string] $DestDatabase
)
Function ConnectionString([string] $ServerName, [string] $DbName)
@EitanBlumin
EitanBlumin / Generate recommendations for clustered indexes.sql
Last active Feb 12, 2020
Use existing non-clustered index usage stats, and missing index stats, to generate clustered-index recommendations for heap tables
View Generate recommendations for clustered indexes.sql
-------------------------------------------------------
------ Generate Clustered Index Recommendations -------
-------------------------------------------------------
-- Author: Eitan Blumin | https://www.eitanblumin.com
-------------------------------------------------------
-- Description:
-- ------------
-- This script finds all heap tables, and "guestimates" a clustered index recommendation for each.
-- The script implements the following algorithm:
--
@EitanBlumin
EitanBlumin / Create Named SQL Server Linked Server.sql
Created Feb 10, 2020
Create a Linked Server to a remote SQL Server, but give it a different name than its actual address
View Create Named SQL Server Linked Server.sql
DECLARE
@ServerAddress [nvarchar](255) = 'MyRemoteServerAddress\SomeNamedInstanceIfYouWant,1433',
@NewServerName [nvarchar](255) = 'MyRemoteServerName',
@RemoteUser [nvarchar](128) = 'remote_user',
@RemotePassword [nvarchar](128) = 'remote_user_password',
@MapLocalLogin [nvarchar](255) = NULL -- name a local login to map to the remote login. If NULL, will map current login
SET @MapLocalLogin = ISNULL(@MapLocalLogin, SUSER_NAME())
DECLARE @ProviderString NVARCHAR(100);
View fix_orphan_users_all_dbs.sql
SET NOCOUNT ON;
DECLARE @db SYSNAME, @user NVARCHAR(MAX), @loginExists BIT, @saName SYSNAME, @ownedSchemas NVARCHAR(MAX);
SELECT @saName = [name] FROM sys.server_principals WHERE sid = 0x01;
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp;
CREATE TABLE #tmp (DBName SYSNAME NULL, UserName NVARCHAR(MAX), LoginExists BIT, OwnedSchemas NVARCHAR(MAX));
exec sp_MsforEachDB '
INSERT INTO #tmp
SELECT ''?'', dp.name AS user_name
, CASE WHEN dp.name IN (SELECT name COLLATE database_default FROM sys.server_principals) THEN 1 ELSE 0 END AS LoginExists
@EitanBlumin
EitanBlumin / Deploy SSRS Reports.ps1
Created Feb 9, 2020
Powershell script to deploy SSRS reports from a folder (all rds and rdl files)
View Deploy SSRS Reports.ps1
param (
[string] $SourceFolder = "SSRS",
[string] $TargetReportServerUri = "http://localhost/ReportServer",
[string] $TargetFolder = "MyReports"
)
$ErrorActionPreference = "Stop"
if ($SourceFolder -eq "") {
$SourceFolder = $(Get-Location).Path + "\"
View Asynchronous_Triggers_ServiceBroker_Example.sql
USE AdventureWorks2008R2
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('Purchasing.usp_AT_uPurchaseOrderDetail', 'P') IS NOT NULL
DROP PROCEDURE Purchasing.usp_AT_uPurchaseOrderDetail;
GO
CREATE PROCEDURE Purchasing.usp_AT_uPurchaseOrderDetail
@EitanBlumin
EitanBlumin / Shrink Database File in Specified Increments.sql
Created Jan 30, 2020
Shrink a database file in specified increments down to a specific size or percentage of used space
View Shrink Database File in Specified Increments.sql
/*
----------------------------------------------------------------------------
Shrink a Database File in Specified Increments
----------------------------------------------------------------------------
Author: Eitan Blumin | https://www.eitanblumin.com
----------------------------------------------------------------------------
Description:
This script uses small intervals to shrink a file (in the current database)
down to a specific size or percentage (of used space).
You can’t perform that action at this time.