timgaunt’s gists

timgaunt / hostname.sql

Created December 3, 2021 14:53

Strip domain from url

	select
	substring(Url,
	(case when charindex('//', Url)= 0 then 1 else CHARINDEX('//', Url) + 2 end),
	case
	when charindex('/', Url, charindex('//', Url) + 2) > 0 then charindex('/', Url, charindex('//', Url) + 2) - (case when charindex('//', Url)= 0 then 1 else charindex('//', Url) + 2 end)
	when charindex('?', Url, charindex('//', Url) + 2) > 0 then charindex('?', Url, charindex('//', Url) + 2) - (case when charindex('//', Url)= 0 then 1 else charindex('//', Url) + 2 end)

timgaunt / colums.sql

Last active July 26, 2021 09:39

List the columns from a table

	SELECT
	c.name [Column Name],
	CONCAT(
	t.name,
	CASE
	WHEN t.name IN ('char', 'varchar','nchar','nvarchar') THEN
	CONCAT('(', IIF(c.max_length=-1, 'MAX', CONVERT(VARCHAR(4),IIF(t.name IN ('nchar','nvarchar'), c.max_length/2, c.max_length))), ')')
	WHEN t.name IN ('decimal','numeric') THEN
	CONCAT('(', CONVERT(VARCHAR(4),c.precision), ',', CONVERT(VARCHAR(4),c.Scale)+')')
	ELSE ''

timgaunt / MediaFiles.sql

Last active May 9, 2021 19:39

Find Umbraco Media Files by Size

	SELECT n.id, n.text, n.createDate, c.contentTypeId, FORMAT(CAST(pd.varcharValue AS DECIMAL(18,2)) / 1024 / 1024, '0.00') AS [FileSizeMB], CONCAT('https://wc.uat.sitedr.co.uk/umbraco#/media/media/edit/', n.id)
	FROM umbracoNode n left JOIN umbracoContent c ON n.id = c.nodeId LEFT JOIN umbracoContentVersion cv ON c.nodeId = cv.nodeId LEFT JOIN umbracoPropertyData pd ON cv.id = pd.versionId AND pd.propertyTypeId=9
	where c.contentTypeId=1032 AND cv.[current]=1
	ORDER BY CAST(pd.varcharValue AS BIGINT) DESC

timgaunt / ColumnSize.sql

Last active March 11, 2021 16:38

Get Size of SQL Columns

timgaunt / MoveFiles.ps1

Created November 30, 2020 11:52

Move files matching a list of strings from one folder to another

	$sourceFolder = "C:\"
	$targetFolder = "C:\"
	$regnos = @(
	"regno"
	)

	Write-Host "Folder: $sourceFolder"

	$folderFiles = Get-ChildItem -Path $sourceFolder -Filter *.jpg -Recurse -File -Name
	foreach ($r in $regnos)

timgaunt / List Products.sql

Created May 16, 2019 11:19

List all Ucommerce products with their properties

	DECLARE @cols AS NVARCHAR(MAX), @nullLessCols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX);

	SET @cols = STUFF((
	SELECT ',' + QUOTENAME(pdf.Name)
	FROM uCommerce_ProductDefinitionField AS pdf
	WHERE pdf.Deleted=0
	ORDER BY pdf.SortOrder
	FOR XML PATH(''), TYPE
	).value('.', 'NVARCHAR(MAX)'),1,1,'')

timgaunt / Swear Words.txt

Created October 10, 2018 17:12

List of swear words

timgaunt / IIS 32bit mode.ps1

Created May 21, 2018 05:05

Update all IIS app pools to run in 32bit mode

	Import-Module WebAdministration

	foreach ($webapp in Get-ChildItem -Path IIS:\AppPools\)
	{
	$name = "IIS:\AppPools\" + $webapp.name
	$item = $item = Get-Item $name \| Select -ExpandProperty enable32BitAppOnWin64
	if ($webapp.enable32BitAppOnWin64 -ne 'True')
	{
	Set-ItemProperty -Path $name -Name enable32BitAppOnWin64 -Value True
	Write-Host "$name updated to enable 32bit" -ForegroundColor Yellow

timgaunt / Move Files Using List Single Line.ps1

Last active May 8, 2018 08:55

Powershell to move from one folder to another based on file list

	$file_list = Get-Content "C:\files.txt"
	$search_folder = "C:\from"
	$destination_folder = "C:\to"

	foreach ($file in $file_list) { $file_to_move = Get-ChildItem -Path $search_folder -Filter $file -Recurse -ErrorAction SilentlyContinue -Force \| % { $_.FullName};if ($file_to_move) { Move-Item $file_to_move $destination_folder; } }

timgaunt / IIS.ps1

Last active August 10, 2018 19:30

List the IIS sites using PowerShell

	Get-WebBinding \| % {
	$name = $_.ItemXPath -replace '(?:.?)name=''([^''])(?:.*)', '$1'
	New-Object psobject -Property @{
	Name = $name
	Binding = $_.bindinginformation.Split(":")[-1]
	}
	} \| Group-Object -Property Name \|
	Format-Table Name, @{n="Bindings";e={$_.Group.Binding -join "`n"}} -Wrap

Tim Gaunt timgaunt