John Zabroski jzabroski

I hereby claim:

To claim this, I am signing this object:

	/***********************************************************
	TSQL to do a quick and dirty look at single-use plans in
	the execution plan cache of a SQL Server.
	************************************************************/

	/* Size of single use adhoc plans in execution plan cache */
	SELECT
	objtype,
	cacheobjtype,
	SUM(size_in_bytes)/1024./1024. as [MB]

	/*


	--Script source found at : http://stackoverflow.com/a/7059579/1387418
	Security Audit Report
	1) List all access provisioned to a sql user or windows user/group directly
	2) List all access provisioned to a sql user or windows user/group through a database or application role
	3) List all access provisioned to the public role

	# Date: 16/02/14
	# Author: John Sansom
	# Description: PS script to generate all SQL Server Agent jobs on the given instance.
	# The script accepts an input file of server names.
	# Version: 1.1
	#
	# Example Execution: .\Create_SQLAgentJobSripts.ps1 .\ServerNameList.txt

	param([String]$ServerListPath)

	<# .SYNOPSIS
	Export of SSRS reports datasources and images
	.DESCRIPTION
	This PowerShell script exports all (or filtered) reports, data sources and images directly from the ReportServer database
	to a specified folder. For the file name the complete report path is used; for file name invalid characters are replaced with a -.
	Reports are exported with .rdl as extension, data sources with .rds and resources without any additional extension.
	Please change the "Configuration data" below to your enviroment.
	Works with SQL Server 2005 and higher versions in all editions.
	Requires SELECT permission on the ReportServer database.
	.NOTES

	-- Taken from: http://sqlblog.com/blogs/hugo_kornelis/archive/2014/07/18/database-mail-and-then-the-smtp-server-changed.aspx
	DECLARE @NewServer sysname = 'NotTelling.mail', -- New SMTP server
	@OldServer sysname = 'MySecret.mail', -- Old SMTP server
	@account_id int;

	DECLARE Cursor_MailAccounts CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
	FOR SELECT account_id
	FROM msdb.dbo.sysmail_server
	WHERE servername = @OldServer; -- Add extra logic here

	# Copyright (c) 2016 Microsoft Corporation. All Rights Reserved.
	# Licensed under the MIT License (MIT)

	function New-RsSubscription {
	<#
	.SYNOPSIS
	This script creates a new reporting subscription.

	.DESCRIPTION
	This script creates a new reporting subscription based on the parameters provided. NOTE: A new subscriptionId will be generated.

	Param (
	[Parameter(Mandatory=$true)] [String]$ServiceName
	)

	<#
	From: https://gallery.technet.microsoft.com/PowerShell-Script-for-8243e5d1
	This is a PowerShell Script to restart a windows service with its dependent services. The dependent services that were not running when the restart request made, will not be started, so that the system services state will remain same after restart service script run.
	#>

	[System.Collections.ArrayList]$ServicesToRestart = @()

	-- Originally from: https://mssqlwiki.com/2012/05/04/copy-database-wizard-or-replication-setup-might-fail-due-to-broken-dependency/
	--Below script will fix the broken dependencies on all the objects
	----------------------------------------------------------------------------
	--List of objects for which referenced objects are missing.
	--ex: View created on table XYZ and table XYZ is dropped
	----------------------------------------------------------------------------
	SELECT OBJECT_NAME (referencing_id),referenced_database_name, referenced_schema_name, referenced_entity_name
	FROM sys.sql_expression_dependencies
	WHERE referenced_entity_name not in (select name from sysobjects)

	using System;
	using System.CodeDom.Compiler;
	using System.Reflection;
	using System.Threading.Tasks;

	/*
	To execute, run
	string code = "your code here";
	if (!code.Contains("return")) code = "return " + code;
	code = code.Trim().TrimEnd(';') + ";";