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:

tcartwright

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_GetDDL]    Script Date: 12/19/2025 2:03:44 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--#################################################################################################
-- Real World DBA Toolkit Version 2019-08-01 Lowell Izaguirre [email protected]
--#################################################################################################

tcartwright

/* EXEC sp_helptext 'dbo.sp_ArchiveTableData' */
USE [master]
GO


/*
    Author: Tim Cartwright
    
    Purpose: 
        Archives large tables using a "nibbling delete" pattern - moving data in small batches