JosiahSiegel / table_schema_sensitivity.sql

Created September 11, 2024 14:15

MSSQL Table Schema and Sensitivity Details

	SELECT
	QUOTENAME(schema_name(o.schema_id)) + '.' + QUOTENAME(t.name) AS [table]
	,QUOTENAME([c].[name]) AS [column_name]
	,IIF(ic.index_id IS NOT NULL, 1, 0) AS [column_in_index]
	,QUOTENAME([i].[name]) AS [index_name]
	,[i].[type_desc] AS [index_type]
	,[sc].information_type AS [info_type]
	,[sc].label AS [sensitivity_label]
	,[ep].name AS [properties_name]
	,[ep].value AS [properties_value]

JosiahSiegel / ddm_auto.sql

Last active September 10, 2024 17:01

Dynamic Data Masking Automation

	/*
	EXECUTE AS USER = 'non-admin-user';

	SELECT *
	FROM masked_table

	REVERT;
	*/

	DECLARE

JosiahSiegel / azure_sql_quick_analysis.sql

Last active September 15, 2024 06:21

Azure SQL Quick Analysis

	SELECT
	req.session_id AS [session],
	ses.program_name AS [program],
	sqltext.TEXT AS [query],
	DB_NAME(req.database_id) AS [database],
	req.status,
	wg.name AS [resource_group],
	req.command,
	CONVERT(varchar(10), (req.cpu_time / 86400000)) + ':' +
	CONVERT(varchar(10), ((req.cpu_time % 86400000) / 3600000)) + ':' +

JosiahSiegel / elastic_agent_jobs.sql

Last active September 11, 2024 17:41

Elastic Agent Jobs

	-- Elastic Agent Jobs
	-- https://sqlkitty.com/elastic-jobs-azure-sql-db/

	-- Add target group
	EXEC jobs.sp_add_target_group 'AzureSQLDBs';

	-- Add single database (or server/elastic pool) to target group
	EXEC jobs.sp_add_target_group_member
	@target_group_name = 'AzureSQLDBs',
	@target_type = 'SqlDatabase',

JosiahSiegel / index_research.sql

Created May 17, 2024 18:09

Index Research

	SELECT TOP 100
	t.NAME AS [TableName],
	SCHEMA_NAME(t.schema_id) AS [SchemaName],
	MAX([data_compression_desc]) AS [Compression],
	MAX(p.rows) AS [RowCounts],
	SUM(a.total_pages) AS [TotalPages],
	SUM(a.used_pages) AS [UsedPages],
	SUM(a.data_pages) AS [DataPages],
	(SUM(a.total_pages) * 8) / 1024 AS [TotalSpaceMB],
	(SUM(a.used_pages) * 8) / 1024 AS [UsedSpaceMB],

JosiahSiegel / ado_items.wiql

Last active May 17, 2024 14:39

Azure DevOps (ADO) Assignments - Best Query (wiql)

	SELECT
	[System.State],
	[System.Id],
	[System.Title],
	[System.IterationLevel2],
	[Microsoft.VSTS.Common.ClosedDate]
	FROM workitemLinks
	WHERE
	(
	[Source].[System.TeamProject] = @project

JosiahSiegel / ssh_key_auth.md

Last active May 15, 2024 15:20

SSH Key Authentication

Generate key pair

ssh-keygen -t ed25519 -b 4096

Restrict private key access

JosiahSiegel / migrate_data.md

Last active May 3, 2024 14:28

PostgreSQL / Citus Migrate Production Data

PostgreSQL/Citus Migrate Production Data

Big Database Migration

⚠️Recommends support request⚠️

Small Database Migration

No support request

1. Set variables

JosiahSiegel / azure_vm_ssh.md

Created February 2, 2024 14:33

Azure VM SSH with AAD (Entra)

Login and install ssh extension

az login
az extension add --name ssh

Create 24 hour auth in ssh config

az ssh config --resource-group myResourceGroup --name myVm --file ./sshconfig

JosiahSiegel / choco.config

Last active August 14, 2023 17:51

Chocolatey developer environment

	<?xml version="1.0" encoding="utf-8"?>
	<packages>
	<package id="7zip.install" />
	<package id="adobereader" />
	<package id="azure-data-studio" />
	<package id="chocolatey" />
	<package id="chocolatey-compatibility.extension" />
	<package id="chocolatey-core.extension" />
	<package id="chocolatey-dotnetfx.extension" />
	<package id="chocolateygui" />

Josiah Siegel JosiahSiegel