MarkPryceMaherMSFT’s gists

MarkPryceMaherMSFT / exec_requests_history.sql

Last active March 10, 2026 12:37

query-insights exec_requests_history

	SELECT [distributed_statement_id],
	[database_name],
	[submit_time],
	[start_time],
	[end_time],
	[is_distributed],
	[statement_type],
	[total_elapsed_time_ms],
	[login_name],
	[row_count],

MarkPryceMaherMSFT / tables-columns-datatypes.sql

Created March 10, 2026 10:47

query to get the datatypes for each column

MarkPryceMaherMSFT / hardensystemtable.sql

Created March 2, 2026 10:20

Harden a System Table

	declare @list varchar(max)
	declare @sql nvarchar(max)
	drop table if exists sys_tables
	create table sys_tables (object_id bigint, tablename varchar(50))

	SELECT @list = STRING_AGG('(' + CAST([object_id] AS VARCHAR(20)) + ', ''' + CAST([name] AS VARCHAR(128)) + ''')', ',') FROM sys.tables
	SET @sql = 'INSERT INTO sys_tables (object_id, tablename) VALUES ' + @list
	--print @sql
	exec sp_executesql @sql

MarkPryceMaherMSFT / sql_monitor_mirroring.sql

Created December 24, 2025 19:46

query to see when tables were last checked for updates

	SELECT * FROM (
	select distinct eventtime,TRIM( replace(replace(replace(replace(
	case when CHARINDEX('END TRY',table_details) > 3 then LEFT(table_details, CHARINDEX('END TRY',table_details)-4)
	else table_details end, ''', N''',','),'N''',''),''';',''),'''','')) as bl_details
	from (
	select *, CHARINDEX('sys.fn_cdc_get_min_lsn(',SQLText) no_,
	SUBSTRING(SQLText,CHARINDEX('sys.fn_cdc_get_min_lsn(',SQLText) + len('sys.fn_cdc_get_min_lsn('),150) sss_
	,
	case when CHARINDEX('sys.sp_cdc_help_change_data_capture',SQLText) > 0 then SUBSTRING(SQLText,CHARINDEX('sys.sp_cdc_help_change_data_capture',SQLText) + len('sys.sp_cdc_help_change_data_capture'),150)
	when CHARINDEX('cdc_get_ddl_history',SQLText) > 0 then SUBSTRING(SQLText,CHARINDEX('cdc_get_ddl_history @capture_instance = ',SQLText) + len('cdc_get_ddl_history @capture_instance = '),150)

MarkPryceMaherMSFT / query_extended_events.sql

Created December 11, 2025 16:54

query to look at extended events

	SELECT
	X.event_xml.value('(event/@name)[1]', 'varchar(50)') AS EventName,
	X.event_xml.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS SQLText,
	X.event_xml.value('(event/action[@name="database_name"]/value)[1]', 'varchar(128)') AS DatabaseName,
	X.event_xml.value('(event/action[@name="username"]/value)[1]', 'varchar(128)') AS UserName,
	X.event_xml.value('(event/@timestamp)[1]', 'datetime') AS EventTime
	FROM sys.fn_xe_file_target_read_file('C:\XE\MonitorTableQueries*.xel', NULL, NULL, NULL) AS F
	CROSS APPLY (SELECT CAST(F.event_data AS XML)) AS X(event_xml)
	ORDER BY EventTime DESC;

MarkPryceMaherMSFT / monitoring_extended_events.sql

Last active December 11, 2025 16:52

Query to monitor the activity from Mirroring

	-- Create Extended Events session
	CREATE EVENT SESSION [MonitorTableQueries]
	ON SERVER
	ADD EVENT sqlserver.sql_statement_completed
	(
	ACTION (
	sqlserver.sql_text,
	sqlserver.database_name,
	sqlserver.username
	)

MarkPryceMaherMSFT / Clean up Extended Events.sql

Created November 18, 2025 19:33

Clean up evented events


	-- Stop the Extended Events session if it is running
	IF EXISTS (
	SELECT 1
	FROM sys.server_event_sessions
	WHERE name = 'MonitorTableQueries'
	)
	BEGIN
	ALTER EVENT SESSION [MonitorTableQueries] ON SERVER STATE = STOP;
	PRINT 'MonitorTableQueries session stopped.';

MarkPryceMaherMSFT / MirroringEventTrace.sql

Created November 18, 2025 19:29

Script to setup an extended events trace for SQL Mirroring


	-- Create Extended Events session
	CREATE EVENT SESSION [MonitorTableQueries]
	ON SERVER
	ADD EVENT sqlserver.sql_statement_completed
	(
	ACTION (
	sqlserver.sql_text,
	sqlserver.database_name,
	sqlserver.username

MarkPryceMaherMSFT / sp_help_change_feed.sql

Created October 9, 2025 19:34

Code to materialise the stored proc sp_help_change_feed

	IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[help_change_feed]') AND type in (N'U')) DROP TABLE [dbo].[help_change_feed]

	create table help_change_feed
	(
	table_group_id uniqueidentifier ,
	table_group_name nvarchar(140) ,
	destination_location nvarchar(512) ,
	destination_credential nvarchar(247) ,
	destination_type nvarchar(247) ,
	workspace_id nvarchar(247) ,

MarkPryceMaherMSFT / ConvertViews.py

Last active October 4, 2025 03:12

This code is for converting spark views to tsql views

	%%pyspark

	import sempy.fabric as fabric
	import struct
	import sqlalchemy
	import pyodbc
	import pandas as pd
	from notebookutils import mssparkutils

	#Function to Return sqlalchemt ODBC Engine, given a connection string and using Integrated AAD Auth to Fabric

Mark Pryce-Maher MarkPryceMaherMSFT