tcartwright

-- Calculate optimal batch size based on actual rows per page
DECLARE @RowsPerPage INT
DECLARE @MaxLocks INT = 4000  -- 80% of 5000 threshold
DECLARE @OptimalBatchSize INT

-- Get rows per page
SELECT @RowsPerPage = 
    CASE 
        WHEN SUM(used_page_count) > 0 
        THEN SUM(row_count) / SUM(used_page_count)

tcartwright

DECLARE @DatabaseName NVARCHAR(128)
DECLARE @SQL NVARCHAR(MAX)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE database_id > 4  -- Excludes system databases (master, tempdb, model, msdb)
    AND state_desc = 'ONLINE'
    AND [name] NOT IN ('DBATools')
    AND is_read_only = 0

tcartwright

DECLARE @table SYSNAME = '[dbo].[TableName]';
DECLARE @sql NVARCHAR(MAX) = N'';
DECLARE @RowCount BIGINT;

-- Get total row count once (no need to scan per column)
SELECT @RowCount = SUM(p.[rows])
FROM sys.partitions AS p
WHERE p.[object_id] = OBJECT_ID(@table)
  AND p.[index_id] IN (0, 1); -- heap or clustered index

tcartwright

/*
-- Brent Ozars script: 
EXEC dbo.sp_BlitzIndex @SchemaName='dbo', @TableName='TableName', @fullOutput = 1
*/

-- makes it very easy to spot possible duplicates that can be combined.
SELECT 
    [i].[name] AS IndexName,
    [i].[type_desc] AS IndexType,
    STUFF(

tcartwright

git rm .gitattributes        # Removes the .gitattributes file from both the working directory and the Git index (staging area).
git add -A                   # Stages all changes: added files, modified files, and deletions (like the .gitattributes removal) for the next commit.
git reset --hard             # Resets the working directory and index to the last committed state — all staged and unstaged changes are lost.

tcartwright

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

/************************************************************
** Change the value of the @server variable here
*************************************************************/
DECLARE @Server VARCHAR(255) = 'servername'

tcartwright

# IMPORTANT: !!Generated certs should NOT be used in production!!
$cert = New-SelfSignedCertificate `
  -Subject "CN=sso.servername.it" `
  -KeyAlgorithm RSA `
  -KeyLength 2048 `
  -CertStoreLocation "Cert:\CurrentUser\My" `
  -NotAfter (Get-Date).AddYears(5) `
  -KeyExportPolicy Exportable `
  -Type Custom `
  -TextExtension @("2.5.29.37={text}1.3.6.1.5.5.7.3.3") # Code signing usage

tcartwright

/*
ORIGINAL SCRIPT: https://lukelowrey.com/sql-server-script-to-automatically-create-indexes-for-foreign-keys/
TDC: script modifications
    - include the schema name when creating the index
    - switched to inner join syntax
    - switched to using concat for string concatenation
    - general cleanup
*/

DECLARE @IndexName TABLE (

tcartwright

SET NOCOUNT ON

DECLARE @current_id INT = 1,
    @db_name sysname,
	@table_name sysname,
	@target_months INT,
	@datetime_column sysname,
	@sql NVARCHAR(MAX),
	@sep VARCHAR(100) = REPLICATE('*', 80)

tcartwright

<# download and install Solarwinds SentryOne Plan Explorer #>
$filenamePlanExplorerInstall = "$env:USERPROFILE\downloads\" + ([string](Get-Date -format "yyyy-MM-dd")) + "_SolarWinds-PlanExplorer.exe"
Start-BitsTransfer -Source 'https://downloads.solarwinds.com/solarwinds/Release/FreeTool/SolarWinds-PlanExplorer.exe' -Destination $filenamePlanExplorerInstall
& $filenamePlanExplorerInstall /install /passive /norestart