NJohnson9402

DECLARE @ver nvarchar(MAX), @cmd nvarchar(MAX);
SELECT @ver = @@VERSION;

SET @cmd = N'CREATE INDEX IX_TableName_Description ON dbo.TableName
    (Column1, Column2)
    INCLUDE (Column3, Column4)
    ';

IF (NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_TableName_Description'
        AND object_id = OBJECT_ID('dbo.TableName')))

NJohnson9402

# AUTHOR: Nate Johnson, @njohnson9402/@natethedba, natethedba.wordpress.com
# LICENSE: https://choosealicense.com/licenses/unlicense/
# TYPE: PowerShell script
# DESCRIPTION/USAGE: example of 2 file copying loops using BITS Transfer cmdlets, targeted at SQL backups
# See corresponding blog post at https://natethedba.wordpress.com/powershell-and-bits/
# ASSUMPTION: your SQL backup files are named with a date component in the form "yyyyMMdd", and use extension ".bak"

$source = "\\BigSQL\Backup\BigSQLInstance\" #network share or local directory
$destiny = "X:\Restore\BigSQLInstance\"     #LOCAL drive destination
$testonly = 1                               #set to 0 to actually do the copying!

NJohnson9402

--A brief demo script about IDs, IDENTITY values, and boxes.
USE tempdb; --> we'll work in TempDB because it's quick, easy, and doesn't require any extra permissions

IF (OBJECT_ID('dbo.IdentityIsPK') IS NOT NULL)
	DROP TABLE dbo.IdentityIsPK;

CREATE TABLE dbo.IdentityIsPK (
    ID int IDENTITY(1,1)
        CONSTRAINT PK_IdentityIsPK PRIMARY KEY CLUSTERED
    , Foo varchar(100) NOT NULL

NJohnson9402

/* The system table that contains TZ info
SELECT tzi.name, tzi.current_utc_offset, tzi.is_currently_dst
FROM master.sys.time_zone_info tzi
*/

--Drop & create tables
IF (OBJECT_ID('dbo.OfficeLocation') IS NOT NULL)
	DROP TABLE dbo.OfficeLocation;
IF (OBJECT_ID('dbo.MyTrans') IS NOT NULL)
	DROP TABLE dbo.MyTrans;

NJohnson9402

--Put this stuff in a new schema so we can easily clean up afterward.
CREATE SCHEMA [play] AUTHORIZATION [dbo];
GO

--Set up our Cards model
--DROP TABLE play.Cards;
CREATE TABLE play.Cards
(
    CardID int IDENTITY(1,1) PRIMARY KEY CLUSTERED
    , Face varchar(2)

NJohnson9402

; AUTHOR: Nate Johnson, @njohnson9402/@natethedba, natethedba.wordpress.com
; LICENSE: https://choosealicense.com/licenses/unlicense/
; TYPE: SQL Server 2016 Configuration File (for command-line installation)
; DESCRIPTION:
; Inline comments are mostly copied from existing file generated by install wizard, with clarification where necessary.
; I use drive D:\ for data files (MDF), L:\  for transaction logs (LDF), T:\ for TempDB, and X:\ for backups.
; Most other options are "normal", i.e. I don't deal with clustering, Availability Groups, or other exotic things.
; I am only installing the database engine and replication components; see FEATURES option for more.
; Read the corresponding blog post at https://natethedba.wordpress.com/automating-sql-installation/
; USAGE:

NJohnson9402

/* NJohnson9402 / natethedba.wordpress.com
Designed for use with RedGate SQLPrompt Snippet Manager.
The $PLACEHOLDERS$ tokens get replaced with values of your choice when you use the snippet in your SSMS editor.
For plain/raw TSQL use, replace the tokens with actual values as desired.

See corresponding blog-post for background. Basically, this is a quick way to "check up on" the status of a
bulk table-copy (data copy from one table to another, potentially across different databases and/or schemas),
by knowing the original start-time, and getting row-counts using `sp_spaceused` for each table (source, destination).
This way, you can get a fairly accurate ETA on when the job will finish, based on how it's done so far.
*/

NJohnson9402

USE master;
DECLARE @CONST_P2 int = 1; --Error log, not Agent log.
DECLARE @NumLogFiles int = 30; --SET THIS APPROPRIATELY!
--^ (there's no way to get it programatically outside of registry-reads/other-xp's, none of which is doable in RDS, AFAIK.

--Example: show events from 7/19/2018 between 2am and 2:30am PDT
DECLARE @StartDate datetime = '2018-07-19T02:00:00'
        , @EndDate datetime = '2018-07-19T02:30:00';

CREATE TABLE #ErrorLogs ([LogDate] datetime, [ProcessInfo] nvarchar(50), [Text] nvarchar(max));

NJohnson9402

USE ReportServer;
--Fetch the subscription settings XML
WITH subscriptionXmL AS (
    SELECT SubscriptionID, OwnerID, Report_OID, Locale, InactiveFlags
        , ExtensionSettings, ExtensionSettingsXML = CONVERT(xml, ExtensionSettings)
        , ModifiedByID, ModifiedDate, [Description], LastStatus
        , EventType, MatchData, LastRunTime, [Parameters]
        , DeliveryExtension, [Version]
    FROM ReportServer.dbo.Subscriptions
)

NJohnson9402

USE [dbName];
SELECT pdb.publisher_db [DB], objs.[type] AS [ObjType], art.destination_object AS [Name], pub.publication [Pub], objs.[PubType]
FROM [distribution].dbo.MSpublisher_databases pdb with (nolock)
JOIN [distribution].dbo.MSarticles art with (nolock)
		ON art.[publisher_db] = pdb.[publisher_db]
JOIN [distribution].dbo.MSpublications pub with (nolock)
		on pub.[publication_id] = art.[publication_id]
		and pub.[publisher_db] = pdb.[publisher_db]
LEFT JOIN (
	SELECT 'table' AS [type], name