Skip to content

Instantly share code, notes, and snippets.

Avatar
🕵️‍♂️
Figuring it out

Eitan Blumin EitanBlumin

🕵️‍♂️
Figuring it out
View GitHub Profile
@EitanBlumin
EitanBlumin / SentryOne_CPU_Utilization_Stats_for_all_Sql_Server_Targets.sql
Created Jan 11, 2021
Script to run in the SentryOne database to get CPU utilization min/max/avg/6σ for all targets
View SentryOne_CPU_Utilization_Stats_for_all_Sql_Server_Targets.sql
USE SentryOne
GO
DECLARE
@SiteName NVARCHAR(1000) = NULL
,@SqlServerName NVARCHAR(1000) = NULL
,@End_date DATETIME = NULL
,@Start_date DATETIME = NULL
,@CounterID SMALLINT = 1858 -- CPU %
,@DefaultDaysBack INT = 90
@EitanBlumin
EitanBlumin / Foreign_Key_Hierarchy_Tree.sql
Created Jan 7, 2021
T-SQL script to retrieve the hierarchy tree for a given table, based on foreign key references.
View Foreign_Key_Hierarchy_Tree.sql
/*
Retrieve Foreign Key Hierarchy Tree
===================================
Author: Eitan Blumin | https://eitanblumin.com | https://madeiradata.com
Date: 2021-01-07
Description:
Retrieve the hierarchy tree for a given table,
based on foreign key references.
Use this script to map out your entity relational structure,
see which foreign keys are dependent on a given table,
@EitanBlumin
EitanBlumin / Delete_Batches_From_Table_By_DateTime_Column.sql
Created Dec 26, 2020
Generic script to delete time based data in batches
View Delete_Batches_From_Table_By_DateTime_Column.sql
DECLARE
@DatabaseName SYSNAME = 'MyDB',
@TableName SYSNAME = 'MyTable',
@DateTimeColumnName SYSNAME = 'MyColumn',
@ThresholdDateTime DATETIME = DATEADD(DAY, -14, GETDATE()),
@BatchSize INT = 10000,
@SleepBetweenBatches VARCHAR(17) = '00:00:00.6'
SET NOCOUNT ON;
@EitanBlumin
EitanBlumin / R_myReadXcelProc.sql
Last active Dec 25, 2020
R stored procedure to load Excel files, by Matteo Lorini
View R_myReadXcelProc.sql
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
Based on sample by Matteo Lorini:
https://www.mssqltips.com/sqlservertip/6622/stored-procedure-in-sql-server-with-r-code/
The readxl package needs to be installed first:
@EitanBlumin
EitanBlumin / SQLDump_File_Examine.sql
Created Dec 8, 2020
T-SQL script to read the contents of a SQLDump file's txt file
View SQLDump_File_Examine.sql
/*
Read contents of a SQLDump file's txt file
=============================================
Author: Eitan Blumin | https://www.madeiradata.com
Date: 2020-12-08
*/
SET NOCOUNT, ARITHABORT, XACT_ABORT ON;
DECLARE @FilePath NVARCHAR(4000), @CMD NVARCHAR(MAX)
-- Use below to read the contents of latest memory dump file (mdmp) generated by the current instance:
@EitanBlumin
EitanBlumin / Powershell_Template_with_Transcript.ps1
Last active Feb 3, 2021
Template Powershell script with built-in transcript log management
View Powershell_Template_with_Transcript.ps1
# when creating a scheduled task to run such scripts, use the following structure example:
# powershell.exe -NoProfile -ExecutionPolicy Bypass -File "C:\Madeira\Powershell_Template_with_Transcript.ps1"
Param
(
[string]$logFileFolderPath = "C:\Madeira\log",
[string]$logFilePrefix = "my_ps_script_",
[string]$logFileDateFormat = "yyyyMMdd_HHmmss",
[int]$logFileRetentionDays = 30
)
Process {
@EitanBlumin
EitanBlumin / Redundant_Indexes.sql
Created Dec 6, 2020
T-SQL script to find redundant indexes within the current database, also outputs index usage stats and drop commands
View Redundant_Indexes.sql
SET NOCOUNT ON;
DECLARE @MinimumRowsInTable INT = 200000;
IF OBJECT_ID('tempdb..#FindOnThisDB') IS NOT NULL DROP TABLE #FindOnThisDB;
;WITH Indexes AS
(
select
sets.schema_id,
sets.table_o_id,
sets.key_column_list,
sets.include_column_list,
@EitanBlumin
EitanBlumin / Invalid_owner_for_system_schema_role_or_database.sql
Created Nov 25, 2020
TSQL script to check for invalid owners of system Roles, Schemas, or Databases
View Invalid_owner_for_system_schema_role_or_database.sql
/*
Invalid owner for a system Role, Schema, or Database
====================================================
Author: Eitan Blumin | Madeira Data Solutions | https://www.madeiradata.com
Date: 2020-11-25
Description:
System roles and schemas must have specific owning users or roles.
For example, all system database roles such as db_owner, db_datawriter, db_datareader, etc. must be owned by dbo.
@EitanBlumin
EitanBlumin / Parse Vulnerability Assessment Result Files into HTML.sql
Last active Jun 18, 2021
Script to parse multiple Vulnerability Assessment Tool result files into an HTML reference list ( https://eitanblumin.com/sql-vulnerability-assessment-tool-rules-reference-list/ )
View Parse Vulnerability Assessment Result Files into HTML.sql
/******************************************************************
Parse SQL Vulnerability Assessment Tool Results
***********************************************
Author: Eitan Blumin | https://www.eitanblumin.com
Description:
Use this script to parse a Vulnerability Assessment Tool
results file into a relational structure and save in an HTML page.
This will output the T-SQL queries used by VAT behind the scenes
and their respective meta-data, as displayed in the VAT.
@EitanBlumin
EitanBlumin / MaxDOP_Configuration_Check.sql
Created Sep 16, 2020
T-SQL script to check and make sure that the configured MAXDOP is in the recommended range based on Microsoft best practice
View MaxDOP_Configuration_Check.sql
/*
Check that the configured value for MAXDOP is in the recommended range, as described in this KB article:
https://support.microsoft.com/en-us/help/2806535/recommendations-and-guidelines-for-the-max-degree-of-parallelism-confi
If @WhatIf = 0 then MAXDOP will automatically be changed to the recommended setting.
*/
-- change this to 1 to only display findings without actually changing the config:
DECLARE @WhatIf BIT = 0;
--------------------------------------