EitanBlumin

/*
Author: Eitan Blumin | https://www.eitanblumin.com
Create Date: 2020-03-18
Description:
  This script will detect currently running sessions in your database which are running DBCC SHRINK commands.
  It will also output the name of any tables and indexes the session is currently locking.
  
  Use this query to find out what causes a SHRINK to run for too long.
  You may need to run it multiple times to "catch" the relevant info.
  Optionally, set @RunUntilCaughtLockInfo to 1 to continuously run until a session with object lock info was caught.

EitanBlumin

/*
===================================================
	Service Broker Sample 1: Parallel Querying
===================================================
Copyright:	Eitan Blumin (C) 2012
Email:		eitan@madeira.co.il
Source:		www.madeira.co.il
Disclaimer:
	The author is not responsible for any damage this
	script or any of its variations may cause.

EitanBlumin

/*
===================================================
	Generate MERGE Statements for All Tables
===================================================
Copyright:	Eitan Blumin (C) 2012
Email:		eitan@madeiradata.com
Source:		www.madeiradata.com
Disclaimer:
	The author is not responsible for any damage this

EitanBlumin

IF OBJECT_ID('tempdb..#sp_help_revlogin2') IS NOT NULL DROP PROCEDURE #sp_help_revlogin2
GO
/*********************************************************************************************
sp_help_revlogin2 V1.2
Eitan Blumin

https://eitanblumin.com | https://madeiradata.com
https://gist.github.com/EitanBlumin/1f19b0b3f59a9220641c559653b90f15
https://github.com/MadeiraData/MadeiraToolbox/blob/master/Utility%20Scripts/sp_help_revlogin2.sql
https://eitanblumin.com/2021/05/11/t-sql-tuesday-138-sp_help_revlogin-is-dead-long-live-sp_help_revlogin2/

EitanBlumin

DROP TABLE IF EXISTS [dbo].[TestTable2];
DROP TABLE IF EXISTS [dbo].[TestTable1];
DROP TABLE IF EXISTS dbo.TestEdgeTable;
DROP TABLE IF EXISTS dbo.TestNodeTable1;
DROP TABLE IF EXISTS dbo.TestNodeTable2;
DROP TABLE IF EXISTS dbo.TestNodeTable3;
GO
IF SCHEMA_ID('EitanTest') IS NOT NULL DROP SCHEMA EitanTest;
GO
CREATE SCHEMA EitanTest AUTHORIZATION dbo;

EitanBlumin

-------------------------------------------------------
------ Generate Clustered Index Recommendations -------
-------------------------------------------------------
-- Author: Eitan Blumin | https://www.eitanblumin.com
-- More info: https://eitanblumin.com/2019/12/30/resolving-tables-without-clustered-indexes-heaps/
-------------------------------------------------------
-- Description:
-- ------------
-- This script finds all heap tables, and "guestimates" a clustered index recommendation for each.
-- The script implements the following algorithm:

EitanBlumin

/*
Author: Eitan Blumin (t: @EitanBlumin | b: eitanblumin.com)
Date: 2020-06-04
Description:
	Use this script to restore from a database backup while easily moving all files to specified folders per file type.
	The script must be run in SQLCMD mode.
	Don't forget to modify the SQLCMD variables as needed.
*/
:setvar DatabaseName MyDatabase
:setvar BackupFilePath H:\MyDatabase_backup_20200602_233000.bak

EitanBlumin

declare @filename nvarchar(200)

select @filename = convert(nvarchar(200), value)
from ::fn_trace_getinfo(null)
where property = 2
and convert(nvarchar(200), value) LIKE '%deadlocks%'

PRINT @filename

select StartTime, convert(xml, TextData) AS Deadlock_Graph, ServerName

EitanBlumin

-- Author: Eitan Blumin | https://www.eitanblumin.com
-- Date: 2020-02-26
-- Description: This is a query to generate rename commands for all system-named default constraints within the current database.
-- The constraints are renamed based on convention of "DF_{TableName}_{ColumnName}"
-- Simply run this query and then copy & paste the entire remediationCommand column to get the script(s).

SELECT
	schemaName = sch.[name],
	tableName = tab.[name],
	columnName = col.[name],

EitanBlumin

IF OBJECT_ID('dbo.ArchivingActivityLog') IS NULL
BEGIN
	CREATE TABLE dbo.ArchivingActivityLog
	(
		Id INT NOT NULL IDENTITY(1,1),
		SourceTable SYSNAME NOT NULL,
		Command NVARCHAR(MAX) NULL,
		StartTime DATETIME NOT NULL CONSTRAINT DF_ArchivingActivityLog_StartTime DEFAULT (GETDATE()),
		EndTime DATETIME NULL,
		RowsMoved INT NULL,