EitanBlumin

DROP TABLE IF EXISTS #Tree;
CREATE TABLE #Tree
(
	object_id INT PRIMARY KEY WITH(IGNORE_DUP_KEY=ON),
	subset_group_id INT,
	referenced_object_id INT NULL
);

-- Insert 1st level tables
INSERT INTO #Tree

EitanBlumin

SELECT
'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(OBJECT_NAME(parent_object_id))
+ ' DROP CONSTRAINT ' + QUOTENAME(name)
FROM sys.foreign_keys
WHERE schema_id IN ( SCHEMA_ID('jobs'), SCHEMA_ID('jobs_internal') )
ORDER BY
	CASE schema_id WHEN SCHEMA_ID('jobs') THEN 1 ELSE 2 END ASC
	
SELECT
'DROP VIEW ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name)

EitanBlumin

DECLARE
	 @FromDate DATETIME2(3)	= NULL
	,@ToDate DATETIME2(3)	= NULL

DECLARE @FileName NVARCHAR(4000)
SELECT @FileName = target_data.value('(EventFileTarget/File/@name)[1]','nvarchar(4000)')
FROM (SELECT CAST(target_data AS XML) target_data FROM sys.dm_xe_sessions s
JOIN sys.dm_xe_session_targets t ON s.address = t.event_session_address
WHERE s.name = N'AlwaysOn_health') ft

EitanBlumin

/*
----------------------------------------------------------------------------
		Grow a Database File in Specified Increments
----------------------------------------------------------------------------
Author: Eitan Blumin | https://www.eitanblumin.com
Creation Date: 2020-03-30
----------------------------------------------------------------------------
Description:
	This script uses small intervals to grow a file (in the current database)

EitanBlumin

DECLARE @CurrDB SYSNAME, @IsInAG BIT, @CMD NVARCHAR(MAX);

-- Find all databases which are either standalone, or SECONDARY in AG
DECLARE dbs CURSOR
LOCAL FAST_FORWARD
FOR
SELECT [name], CASE WHEN replica_id IS NULL THEN 0 ELSE 1 END
FROM sys.databases
WHERE database_id <> 2
AND state_desc = 'ONLINE'

EitanBlumin

/*
Re-Number Identity Column
=================================
Author: Eitan Blumin | https://www.eitanblumin.com
Create Date: 2020-03-24
Description:
	Use this script to re-number a table with an identity column, which has very large number gaps.
	The specified parameter @ChunkSize must be smaller than the current minimum value
	in the table.
*/

EitanBlumin

SELECT
  databse_name = DB_NAME()
, file_name
, check_file_total_size = file_total_size
, check_file_total_used_space = file_total_used_space
, check_file_total_unused_pages = file_total_unused_pages
, agg_file_total_reserved_pages = file_total_reserved_pages
, agg_file_total_unused_pages = SUM(pt.consecutive_unused_pages) OVER (PARTITION BY file_id)
, pt.*

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.
*/

EitanBlumin

-- Author: Eitan Blumin | https://www.eitanblumin.com
-- Date: 2020-02-26
-- Last Update: 2020-04-07
-- Description: Collect T-SQL Events using an Extended Events Buffer

SET NOCOUNT ON;

DECLARE
  @SourceLinkedServer SYSNAME
, @MinimumDurationMilliSeconds BIGINT

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],