dhmacher

--- 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

--- 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/

dhmacher

-- 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

# 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)))

dhmacher

-------------------------------------------------------------------------------
---
--- 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

# 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

/*
    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

# 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

Function Invoke-ParamQuery {
    param(
        [String]$Query,
        $Parameters=@{},
        [Data.SqlClient.SqlConnection]$Conn,
        [int]$Timeout=3,
        [switch]$CloseConn,
        [switch]$DiscardResults
    )
    if ($conn.State -eq "Closed") {

dhmacher

SELECT j.[name] AS Job,
       s.step_id AS Step,
       s.step_name AS [Step name],
       s.subsystem AS [Subsystem],
       s.[command],
       x2.p AS [SSIS path],
       env.environment_name AS [Environment name]
FROM msdb.dbo.sysjobs AS j
INNER JOIN msdb.dbo.sysjobsteps AS s ON j.job_id=s.job_id
OUTER APPLY (