Skip to content

Instantly share code, notes, and snippets.

Avatar

Daniel Hutmacher dhmacher

View GitHub Profile
View Find primary key candidates.sql
-- Source: https://sqlsunday.com/2017/02/21/finding-primary-key-candidates/
IF (OBJECT_ID('dbo.FindPrimaryKey') IS NULL)
EXEC('CREATE PROCEDURE dbo.FindPrimaryKey AS --');
GO
/*
This stored procedure is used to identify primary key candidates.
Copyright Daniel Hutmacher under Creative Commons 4.0 license with attribution.
@dhmacher
dhmacher / Agent job visualization.sql
Created Oct 22, 2021
Visualize agent jobs as a gannt chart
View Agent job visualization.sql
--- Read more: https://sqlsunday.com/2016/11/17/visual-representation-of-sql-server-agent-jobs/
/*
Copyright Daniel Hutmacher under Creative Commons 4.0 license with attribution.
http://creativecommons.org/licenses/by/4.0/
Source: http://sqlsunday.com/downloads/
DISCLAIMER: This script may not be suitable to run in a production
@dhmacher
dhmacher / LoadTableBlobs.sql
Created Oct 22, 2021
Imports an XML blob into a set of relational tables.
View LoadTableBlobs.sql
--- Read this first: https://sqlsunday.com/2016/06/16/copying-data-with-foreign-keys-and-identity-columns/
IF (OBJECT_ID('dbo.LoadTableBlobs') IS NULL) EXEC('CREATE PROCEDURE dbo.LoadTableBlobs AS --')
GO
/*
Copyright Daniel Hutmacher under Creative Commons 4.0 license with attribution.
http://creativecommons.org/licenses/by/4.0/
Source: http://sqlsunday.com/downloads/
View Decrypt T-SQL modules.sql
-- More information: https://sqlsunday.com/2013/03/24/decrypting-sql-objects/
SET NOCOUNT ON
DECLARE @owner sysname='dbo', @name sysname='sp_someprocedure';
-----------------------------------------------------------
--- Declarations:
DECLARE @offset int=1;
DECLARE @datalength int;
@dhmacher
dhmacher / Extract-WooCommerceOrders.ps1
Created Oct 12, 2021
Generates a SIE accounting file from WooCommerce sales orders.
View Extract-WooCommerceOrders.ps1
# Provided as-is, without any warranty, implied or express.
# General:
$file = "Precon-" + (Get-Date).toString("yyyyMMdd-HHmmss") + ".se"
$baseUri = "https://example.com/wp-json/wc/v3/"
# WooCommerce authentication:
$key = "cs_aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"
$secret = "cs_aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"
$base64AuthInfo = [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(("{0}:{1}" -f $key, $secret)))
View dbo.Partition_Boundary_Values.sql
-------------------------------------------------------------------------------
---
--- Reverse $PARTITION function - returns the boundary values for a given
--- partition function and partition number.
---
--- Returns:
---
--- Lower_Boundary sql_variant NULL for first partition
--- Lower_Boundary_Condition varchar(2) "<=" or "<"
@dhmacher
dhmacher / ConvertTo-UTF8
Created Jun 30, 2021
Converts an ISO-8859-1 string to UTF-8. Handy on when you receive a response from a web API that does not specify the codepage.
View ConvertTo-UTF8
# https://stackoverflow.com/a/53034595/5471286
function ConvertTo-UTF8 {
param(
[Parameter(ValueFromPipeline)] [string]$ISOString
)
return ([Text.Encoding]::UTF8.GetString( `
[Text.Encoding]::GetEncoding(28591).GetBytes($ISOString)))
}
@dhmacher
dhmacher / Widest tables by avg row bytes.sql
Created Jun 29, 2021
Show the widest tables (or indexed views) by average bytes/row
View Widest tables by avg row bytes.sql
/*
Find the widest tables by bytes/row
*/
SELECT OBJECT_SCHEMA_NAME(ps.[object_id])+N'.'+OBJECT_NAME(ps.[object_id]) AS [Object],
ix.[name] AS [Index],
ps.partition_number AS [Partition],
p.data_compression_desc AS [Compression],
REPLACE(CONVERT(varchar(20), CAST(SUM(ps.row_count) AS money), 1), '.00', '') AS [Row count],
REPLACE(CONVERT(varchar(20), FLOOR(CAST(SUM(8192.*ps.in_row_used_page_count) /NULLIF(SUM(ps.row_count), 0) AS money)), 1), '.00', '') AS [In-row, bytes/row],
@dhmacher
dhmacher / Check-ImageUrls.ps1
Last active May 23, 2021
Validate all image URLs in a WordPress blog export
View Check-ImageUrls.ps1
# Export your WordPress blog to an XML file. Then:
$xmlFile = "./export/sqlsundaycom.wordpress.2021-05-23.001.xml"
$matches = Select-String -Path $xmlFile -Pattern '(?<=src\=\")http(.*?)(?=\")' -AllMatches `
| % { $_.Matches }
| % { $_.Value }
| Sort-Object -Unique
foreach ($url in $matches) {
#$url
@dhmacher
dhmacher / Invoke-ParamQuery.ps1
Created Apr 10, 2021
Run a SQL Server query using .NET (without the "SqlServer" module)
View Invoke-ParamQuery.ps1
Function Invoke-ParamQuery {
param(
[String]$Query,
$Parameters=@{},
[Data.SqlClient.SqlConnection]$Conn,
[int]$Timeout=3,
[switch]$CloseConn,
[switch]$DiscardResults
)
if ($conn.State -eq "Closed") {