tcartwright

Clear-Host
#Requires -RunAsAdministrator
Import-Module WebAdministration -ErrorAction Stop
$apphost = "MACHINE/WEBROOT/APPHOST"

$standardFlags = "Date,Time,ClientIP,UserName,SiteName,ComputerName,ServerIP,Method,UriStem,UriQuery,HttpStatus,HttpSubStatus,Win32Status,BytesSent,BytesRecv,TimeTaken,ServerPort,UserAgent,Referer,ProtocolVersion,Host"

$customFields = @(
    @{ name = "X-Forwarded-For"; src = "X-Forwarded-For";  type = "RequestHeader"  }
    @{ name = "Correlation-ID";  src = "X-Correlation-ID"; type = "RequestHeader"  }

tcartwright

SET NOCOUNT ON;

DECLARE @DryRun bit = 1;   -- 1 = print only, 0 = execute

-- Roles to ensure exist in every targeted database.
--   Permissions   : granted to the role (raw permission list).
--   SchemaPattern : comma-delimited list of LIKE patterns matched against each DB's schemas.
--                   Wildcards: %  any string,  _ any single char,  [ ] char set/range.
--                   Use \ to escape a literal wildcard (ESCAPE is set below). e.g. N'dbo'
--                   or N'dbo,Reporting%' or N'%' (all user schemas).

tcartwright

SET NOCOUNT ON;

DECLARE @LoginName sysname = N'login-name';
DECLARE @DryRun    bit     = 1;   -- 1 = print only, 0 = execute

DECLARE @Excluded TABLE (DatabaseName sysname PRIMARY KEY);
INSERT INTO @Excluded (DatabaseName) VALUES
    (N'master'),
    (N'tempdb'),
    (N'model'),

tcartwright

/* ============================================================================
   Find tables where the primary key is NONCLUSTERED but the table has a
   separate CLUSTERED index (i.e. the clustering key is something other than
   the PK).

   For each matching table, returns:
     - schema and table name
     - the PK index name, its key columns, and any included columns
     - the clustered index name, its key columns, and any included columns
       (note: clustered indexes don't support INCLUDE at DDL time, so the

tcartwright

/* Batched identity-PK FILLFACTOR=100 rebuild runner (with backup pacing)
 * GENERATED BY: *claude*
 *
 * PURPOSE
 *   Rebuilds every identity-keyed clustered index in the CURRENT database to
 *   FILLFACTOR = 100. Compression is NOT touched here — whatever compression
 *   level each index has today (NONE / ROW / PAGE) is preserved by omitting
 *   the DATA_COMPRESSION clause from the REBUILD. Handle PAGE compression in
 *   a separate script.
 *

tcartwright

-- Finds cached plans whose showplan XML contains a UDF reference,
-- and tags them by DML statement type (SELECT/INSERT/UPDATE/DELETE/MERGE).

WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
SELECT
    udf_ref.value('@Database', 'sysname') AS udf_database,
    udf_ref.value('@Schema',   'sysname') AS udf_schema,
    udf_ref.value('@Table',    'sysname') AS udf_name,   -- showplan (oddly) labels UDF names with @Table
    CASE
        WHEN UPPER(LTRIM(stmt_text)) LIKE 'INSERT%' THEN 'INSERT'

tcartwright

<#
.SYNOPSIS
    Cross-platform .NET runtime or SDK installer.

.DESCRIPTION
    All-in-one script that queries Microsoft's release index, downloads
    the latest installers, and installs them. Skips both download and
    install when the target version is already present.

    Works on Windows, Linux, and macOS under PowerShell 7 (pwsh).

tcartwright

-- expects rows to be in reverse order, if not in reverse order, then need to switch the columns in the datediff


		CONCAT(
			DATEDIFF(MILLISECOND, LEAD([all_logs].[timestamp]) OVER (ORDER BY [all_logs].[Id] DESC), [all_logs].[timestamp]) / 86400000, '.',
			CONVERT(VARCHAR, DATEADD(MILLISECOND, DATEDIFF(MILLISECOND, LEAD([all_logs].[timestamp]) OVER (ORDER BY [all_logs].[Id] DESC), [all_logs].[timestamp]) % 86400000, 0), 114)
		) AS [TimeDiff]

tcartwright

/*
    Find cached query plans containing implicit conversions (CONVERT_IMPLICIT)
    Sorted by total subtree cost (descending), then total elapsed time (descending)
    
    Targets the plan cache via sys.dm_exec_query_stats + sys.dm_exec_query_plan
    Parses the XML query plan for CONVERT_IMPLICIT warnings and expressions
*/

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

tcartwright

-- ============================================================
-- Identity Column Capacity Report
-- ============================================================
-- Reports on all tables with identity columns that have 100,000
-- or more rows, showing how much ID space remains and when each
-- table is projected to exhaust its identity range.
--
-- For each qualifying table this query provides:
--   - Current identity value and data type maximum