tcartwright

Converted into a module: https://github.com/tcartwright/tcdbtools

PS Gallery link: https://www.powershellgallery.com/packages/tcdbtools/

https://github.com/tcartwright/tcdbtools/blob/main/tcdbtools/sql/FindInvalidSettings.sql

tcartwright

Clear-Host
# https://intellitect.com/enter-vsdevshell-powershell/
$installPath = &"C:\Program Files (x86)\Microsoft Visual Studio\Installer\vswhere.exe" -property installationpath | select -First 1
$vsShellPath = (Join-Path $installPath "Common7\Tools\VsDevCmd.bat")
#Enter-VsDevShell -VsInstallPath $installPath -SkipAutomaticLocation # DEPENDS ON a certain verions of VS to be installed.
# https://github.com/Microsoft/vswhere/issues/150
& "${env:COMSPEC}" /s /c "`"$vsShellPath`" -no_logo && set" | foreach-object {
    $name, $value = $_ -split '=', 2
    Write-Host "Setting: env:$name $value"
    Set-Content env:"$name" $value

tcartwright

Save Email

This code lets you save your email from the Outlook application either to your hard drive or you archive folder. It will only save or archive the email if it has not done so previously.

Saving to the archive folder - Recommended method

A copy of the email will be made, and moved to the Archive folder in the exact same folder structure it came from. When saving emails to the archive folder it will add a custom property to the original email of X-Archived with a value of the current date. When scanning for emails again, it will skip over any emails with that property.

Saving to the hard drive

tcartwright

Converted into a module: https://github.com/tcartwright/tcdbtools

PS Gallery link: https://www.powershellgallery.com/packages/tcdbtools/

tcartwright

SELECT ISNULL((
	SELECT dns_name + '\' + CAST(SERVERPROPERTY ('InstanceName') AS VARCHAR(512))
	FROM sys.availability_group_listeners agl
	JOIN sys.availability_group_listener_ip_addresses aglip
		ON agl.listener_id = aglip.listener_id
	JOIN sys.dm_exec_connections c
		ON aglip.ip_address = c.local_net_address
	WHERE session_id = @@SPID
), @@SERVERNAME)

tcartwright

-- more info: https://sqlity.net/en/2344/create-login-with-hashed-password/
 DECLARE @gen_auto_fix BIT = 1,	/* IF 1 then the script to autofix the login in all the databases will be output */
 	@drop_if_exists BIT = 0		/* IF 1 then the login will be dropped if exists, else it will only be dropped if the login and sid do not match */

DECLARE @tab VARCHAR(1) = CASE WHEN @drop_if_exists = 1 THEN CHAR(9) ELSE '' END,
	@crlf CHAR(2) = CONCAT(CHAR(13), CHAR(10))

SELECT [sp].[name], [sp].[create_date], [sp].[modify_date], [sp].[is_disabled], [sp].[type_desc], @@SERVERNAME AS [server_name], [create_or_alter_sql] = 
@tab + '/*' + REPLICATE('*', 40) + REPLICATE('*', LEN(fn.generated_context)) + REPLICATE('*', 40)  + '*/' + @crlf +
@tab + '/*' + REPLICATE('*', 40) + fn.generated_context + REPLICATE('*', 40)  + '*/' + @crlf +

tcartwright

IF OBJECT_ID (N'dbo.GetQuarterStartAndEnd') IS NOT NULL
    DROP FUNCTION dbo.GetQuarterStartAndEnd
GO

CREATE FUNCTION dbo.GetQuarterStartAndEnd (@date DATETIME, @quarter TINYINT)
RETURNS TABLE 
AS RETURN (
	SELECT [QuarterStart] = DATEADD(QUARTER, fn.quarter_diff + (@quarter - 1), 0),       
		[QuarterEnd] = DATEADD(QUARTER, fn.quarter_diff + @quarter, 0) 
	FROM (VALUES (DATEFROMPARTS(YEAR(@date), 1, 1))) t (dt)

tcartwright

DECLARE @sql NVARCHAR(MAX) = 'SELECT ' + 
 STUFF((SELECT TOP 100 PERCENT ', [is_' + sp.name + '] = ' + 'IS_SRVROLEMEMBER(''' + sp.name + ''')' 
	FROM sys.server_principals sp WHERE type = 'R' ORDER BY sp.is_fixed_role DESC, sp.create_date FOR XML PATH('')), 1, 2, '')

EXEC (@sql)

SET @sql = 'SELECT ' + 
 STUFF((SELECT TOP 100 PERCENT ', [is_' + dp.name + '] = ' + 'IS_MEMBER(''' + dp.name + ''')' 
	FROM sys.database_principals dp WHERE type = 'R' ORDER BY dp.sid FOR XML PATH('')), 1, 2, '')

tcartwright

cd "%~dp0"
del /q *.vsix

powershell.exe -ExecutionPolicy Bypass -NoLogo -NoProfile -file "%~dp0vsts_extension_update_packages.ps1"

powershell.exe -ExecutionPolicy Bypass -NoLogo -NoProfile -file "%~dp0vsts_extension_update_versions.ps1"

powershell.exe -ExecutionPolicy Bypass -NoLogo -NoProfile -file "%~dp0vsts_extension_make.ps1"

tcartwright

IF OBJECT_ID('tempdb..#constraints_with_functions') IS NOT NULL BEGIN 
	DROP TABLE #constraints_with_functions 
END
GO
CREATE TABLE [#constraints_with_functions](
	[database_name] sysname NOT NULL,
	[constraint_name] [sysname] NOT NULL,
	[definition] [nvarchar](max) NULL,
	[object_name] [sysname] NOT NULL,
	[object_id] [int] NOT NULL,