Skip to content

Instantly share code, notes, and snippets.

View paschott's full-sized avatar

Peter Schott paschott

12 followers · 6 following
View GitHub Profile
@paschott
paschott / calendar_table.sql
Last active March 25, 2024 14:29
Calendar Table and Population
/* Adapted from ChilledSql.com */
/* https://www.chilledsql.com/welcome/tip_category_dateandtime/tip_detail_dateandtime_createmastercalendartable */
/* Update the Start/End dates as appropriate for your needs */
/* This is done after all creates */
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
SET ARITHABORT OFF
@paschott
paschott / Generate_Spelling_Audio.py
Last active November 15, 2023 00:45
Text to Speech for creating a practice spelling bee audio file
# requires gtts
# requires pydub
# requires ffmpeg to be installed on the system
from gtts import gTTS
from pydub import AudioSegment
import os
def generate_word_audio(word, output_folder='audio'):
if not os.path.exists(output_folder):
@paschott
paschott / Restore-InstallerFiles.ps1
Created July 18, 2023 14:50
This script was designed as a fix Windows "Installer" by restoring the missing Package(*.msi)/Patches(*.msp) files with the following steps: 1. Identifying the missing files. 2. Crawl the missing files from specified folder or other healthy machine.
<#
Code Type: Function
Description: Restore the missing Package(*.msi)/Patches(*.msp) files from another remote source (machine or folder).
Author: Ahmad Gad
Contact Email: ahmad.gad@jemmpress.com, ahmad.adel@jemmail.com
WebSite: http://ahmad.jempress.com
Created On: 21/09/2016
Updated On: 11/03/2017
Title: Restore-InstallerFiles
Minimum PowerShell Version: 2.0
@paschott
paschott / AzureSQLPermissions.sql
Last active September 26, 2023 16:15
Get Azure SQL Database Permissions
/* Get all db principals and their roles for Azure SQL */
SELECT
@@ServerName as ServerName,
DB_NAME() as DatabaseName,
pr.name AS PrincipalName,
pr.type_desc AS PrincipalType,
r.name AS RoleName,
dp.state_desc AS RoleState,
NULL as PermissionName,
NULL as PermissionState,
@paschott
paschott / Find-UnusedDatabases.ps1
Last active November 15, 2022 19:09
Steps through all Azure SQLDB Servers and databases to find user-specific index usage
import-module az.sql
import-module az.resources
import-module importexcel
# Update-AzConfig -DisplayBreakingChangeWarning $false
# sign in to Azure account
Connect-AzAccount
#Folder containing sql script and place to create Excel file
@paschott
paschott / AzureElasticJobTest.ps1
Created November 8, 2022 20:10
Scratch work for SQL Elastic Jobs in Azure w/ a PowerShell script
import-module az.sql
<# TODO
Based on:
https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-jobs-powershell-create?view=azuresql
* Add "Get" commants to top of script as appropriate
* Be able to loop through set of servers
* Document steps and/or separate files
* Parameterize passwords for master & job user accounts
@paschott
paschott / Set up AG Job Steps.sql
Created May 31, 2022 13:51
Sets up a job to add a check for whether the AG is primary or not when determining whether jobs should run.
use master
go
-- Adds a first step to specified job, which checks whether running on Primary replica
create procedure dbo.AddAGPrimaryCheckStepToAgentJob
@jobname nvarchar(128)
as
set nocount on;
@paschott
paschott / Late-Arriving-Customer.sql
Created May 25, 2022 15:32
Example of handling a late-arriving customer as part of an SSIS Partial Cache Lookup Transform
DECLARE @CustomerID INT
SELECT @CustomerID = ?
IF NOT EXISTS (SELECT *
FROM WideWorldImportsDW.Dimension.Customer
WHERE [WWI Customer ID] = @CustomerID)
INSERT WideWorldImportersDW.Dimension.Customer
([WWI Customer ID],
Customer,
[Bill To Customer],
@paschott
paschott / ExportUsers.ps1
Created December 14, 2021 20:05
Export SQL Users/Logins/Role Members into an Excel spreadsheet for reporting purposes
import-module dbatools
import-module ImportExcel
$servers="server1"
$date = get-date #format yyyymmdd
foreach ($server in $servers) {
$ExcelFile = "C:\path\$server_Users_And_Logins_$date.xlsx"
Get-DbaDbRoleMember -SqlInstance $server | Export-Excel -Path $ExcelFile -WorksheetName UserRoleMembers -AutoSize -AutoFilter -ExcludeProperty @("ItemArray", "RowError", "RowState", "Table", "HasErrors") -BoldTopRow
@paschott
paschott / ScriptDBUserPermissions.ps1
Created July 19, 2021 22:13
Import-Module dbatools
Import-Module sqlserver #used for Invoke-SqlCmd to run scripts w/ the "GO" batch separator
$CurrentDate = (Get-Date).ToString("yyyyMMdd_hhmmss")
$Servername = "localhost"
$databases = 'CSV_Set_Of_DB_Names'
#Get permissions for databases