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

tcartwright

DECLARE @LoginName NVARCHAR(128) = 'loginname';  -- Change this
DROP TABLE IF EXISTS #ScriptOutput;

CREATE TABLE #ScriptOutput (
    SortOrder INT IDENTITY(1,1),
    ScriptLine NVARCHAR(MAX)
);

-- =============================================
-- Part 1: Script the LOGIN creation

tcartwright

#Requires -Version 5.1
<#
.SYNOPSIS
    Prunes upstream-cached NuGet packages from an Azure Artifacts feed while preserving internal packages.

.DESCRIPTION
    This script identifies packages in your Azure Artifacts feed that originated from upstream sources
    (like nuget.org) and deletes old versions, keeping only a specified number of recent versions.
    Internal packages (those published directly to your feed) are not touched.

tcartwright

trigger: none

variables:
  cleanPoolName: ${{ replace(replace(replace(parameters.agentPool, '/', '_'), '\', '_'), ':', '_') }}
  cleanAgentName: ${{ replace(replace(replace(replace(parameters.agentName, '*', 'ANY'), '/', '_'), '\', '_'), ':', '_') }}
  reportFile: $(Build.ArtifactStagingDirectory)\BuildServerReport.txt

name: 'BuildServerReport_${{ variables.cleanPoolName }}_${{ variables.cleanAgentName }}'

parameters: