Skip to content

Instantly share code, notes, and snippets.

View paschott's full-sized avatar

Peter Schott paschott

View GitHub Profile
SELECT
objects.name AS Table_name,
indexes.name AS Index_name,
dm_db_index_usage_stats.user_seeks,
dm_db_index_usage_stats.user_scans,
dm_db_index_usage_stats.user_updates
FROM
sys.dm_db_index_usage_stats
INNER JOIN sys.objects ON dm_db_index_usage_stats.OBJECT_ID = objects.OBJECT_ID
INNER JOIN sys.indexes ON indexes.index_id = dm_db_index_usage_stats.index_id AND dm_db_index_usage_stats.OBJECT_ID = indexes.OBJECT_ID
--Get SQL Agent Job names for SSRS Subscriptions
SELECT Schedule.ScheduleID AS JobName,
[Catalog].Name AS ReportName,
Subscriptions.Description AS Recipients,
[Catalog].Path AS ReportPath,
StartDate,
Schedule.LastRunTime
FROM [ReportServer].dbo.ReportSchedule
INNER JOIN [ReportServer].dbo.Schedule
ON ReportSchedule.ScheduleID = Schedule.ScheduleID
@paschott
paschott / New-SQLServerCertificate.ps1
Created December 10, 2020 01:08
Passes a CSR to the Certificate Authority, downloads the certificate, passes it to the target server, then imports and sets it for SQL Server
#requires dbatools
$server = "servername"
$localCertPath = "C:\CertificateRequests"
$remoteCertPath = "C:\CertificateRequest\"
$adminuser = Import-Clixml C:\user.cred #stored credentials to access remote server
# Generate the CSR and download locally
$session = New-PSSession $server -Credential $adminuser
@paschott
paschott / Generate-CSR.ps1
Created November 24, 2020 22:38
PowerShell script to generate a Certificate Request for a server using certain criteria
#Create new Certificate Request for SQL Server security
# Should be made into a function at some point
# Needs to be able to handle Cluster names/IP addresses
#Set location of the server
$Location = "City"
$State = "State"
$OU = "OU"
$Company = "Organization"
@paschott
paschott / usp_PurgeSSISCatalogLogs.sql
Created October 28, 2020 18:32
Creates stored procedure to purge SSIS catalog in batches of rows from child to parent.
use SSISDB
GO
CREATE PROC dbo.usp_PurgeSSISCatalogLogs
@RowsToDelete int = 5000
AS
BEGIN --Proc
/*
Script name: Purge SSIS Catalog log tables
Author: Tim Mitchell (www.TimMitchell.net)
@paschott
paschott / InstallApps.ps1
Last active March 26, 2024 15:46
Use Chocolatey to install some common apsp for MS SQL work and related
#Note - will want to install "Chocolatey" first in order to use this file to install software.
# https://chocolatey.org/docs/installation
# Open a command prompt (cmd) and run the following:
# @"%SystemRoot%\System32\WindowsPowerShell\v1.0\powershell.exe" -NoProfile -InputFormat None -ExecutionPolicy Bypass -Command "iex ((New-Object System.Net.WebClient).DownloadString('https://chocolatey.org/install.ps1'))" && SET "PATH=%PATH%;%ALLUSERSPROFILE%\chocolatey\bin"
# ..
# or open a PowerShell Admin prompt and run:
# Set-ExecutionPolicy Bypass -Scope Process -Force; iex ((New-Object System.Net.WebClient).DownloadString('https://chocolatey.org/install.ps1'))
# After Chocolatey is installed, open a PS prompt in Administrator mode,
@paschott
paschott / TSQLDeleteData.sql
Last active June 15, 2020 22:47
Delete rows from a table in batches. Do not run during set times. Pause between loops.
DECLARE @Time TIME,
@RowsToDelete INT,
@PurgeDataPriorToDate DATETIME2,
@RowsDeleted INT,
@EndLoopTime TIME,
@StartLoopTime TIME
SELECT @RowsToDelete = 5000
SELECT @EndLoopTime = '20:00:00'
SELECT @StartLoopTime = '08:00:00'
--TableName:Backup_MostRecent
;with
db as (
select [Instance] = @@SERVERNAME,
[Database] = name,
[RecoveryMode] = DATABASEPROPERTYEX(name, 'Recovery'),
[CreationTime] = crdate,
[Status] = DATABASEPROPERTYEX(name, 'Status')
from master..sysdatabases
where name!='tempdb'
@paschott
paschott / Get-Mirroring-Information.sql
Created May 20, 2020 16:50
Mirroring information for all databases on a SQL Server
SELECT DISTINCT
CAST(SERVERPROPERTY('ServerName') as nvarchar(100)) AS Principal,
m.mirroring_partner_instance AS Mirror,
DB_NAME(m.database_id) AS DatabaseName,
SUM(f.size*8/1024) AS DatabaseSize,
CASE m.mirroring_safety_level
WHEN 1 THEN 'HIGH PERFORMANCE'
WHEN 2 THEN 'HIGH SAFETY'
END AS 'OperatingMode',
RIGHT(m.mirroring_partner_name, CHARINDEX( ':', REVERSE(m.mirroring_partner_name) + ':' ) - 1 ) AS Port
--Adapted from http://www.chilledsql.com/welcome/tip_category_dateandtime/tip_detail_dateandtime_createmastercalendartable
--If this is an existing table, do not drop/recreate the table, just adjust dates and run the appropriate population script
--Adjust code to ignore dates already in the calendar table.
SET NOCOUNT ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET ARITHIGNORE ON