NJohnson9402

# AUTHOR: Nate Johnson, @njohnson9402/@natethedba, natethedba.wordpress.com
# LICENSE: https://choosealicense.com/licenses/unlicense/
# TYPE: PowerShell script
# DESCRIPTION/USAGE:
# There are TWO static placeholders that you need to change/type-in when you want to use this,
# they both start with <PATH TO ...>.  I also added a #CHANGE THIS! comment to the end of their lines.
# The first one is for your installer config .ini file
# (see other gist at https://gist.github.com/NJohnson9402/a3c13429a055771efd26eefa66c69d62).
# The second is for the location of your SQL server installation media (ISO).
# See corresponding blog post at https://natethedba.wordpress.com/automating-sql-installation/

NJohnson9402

                    *\ |*       ___________
                      \|    ___/ I___I___I_\___
                           /___I___I___I___I___\    Incoming!!
                          /__I___I___I___I___I__\  /
   ___                   /_I___I___I___I___I___I_\                   ___
  /   \     __          /____I___/       \___I____\          __     /   \
 /     \   /  \   _    /___I____/    O    \____I___\    _   /  \   /     \
/       \_/    \_/ \__/__I___I__|____^____|__I___I__\__/ \_/    \_/       \

NJohnson9402

/* NJohnson9402 / natethedba.wordpress.com
Primarily 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.

Typical use-case involves creating a temp-table first, to store your "target table PK values" along with an
Identity value to loop over.  You then fill that temp-table with PK values from your target table, so you
can act on those rows.

For example:

NJohnson9402

/* NJohnson9402 / natethedba.wordpress.com
Uses system catalog views and DMVs to find trigger table and column dependencies;
tailored as "audit trail" example, but applicable to most trigger situations.
Triggers always depend on & are a child object of the table on which they exist.
In this example, the predicate "OBJECTPROPERTY(trig.object_id, 'ExecIsUpdateTrigger') = 1"
restricts the view only to "ON/AFTER UPDATE" triggers; but you could easily extrapolate
to include the other types (INSERT, DELETE) as well, using the same model;
just replace 'ExecIsUpdateTrigger' with 'ExecIsInsertTrigger' etc.
*/
SELECT [Trigger] = trig.name

NJohnson9402

/* NJohnson9402 / natethedba.wordpress.com
An example of how to rename a database by work-around involving backup, restore, detach, attach.
Basically the idea here is that 'OriginalDB' is in heavy use, and can't tolerate much "downtime",
but "readonly time" is OK for a bit.
Drive letters for clarification in this case: 'D' for slow data, 'L' for slow tlog,
'E' for fast data, 'M' for fast tlog. The 'fast' storage system is our destination.
*/
USE master;
EXEC OriginalDB.sys.sp_helpfile;

NJohnson9402

/* NJohnson9402 / natethedba.wordpress.com
An example of how to check for orphaned database users (users that do not map to a server login).
Basically the idea here is to take the output of `sys.sp_helpuser` and utilize it to find & fix.
There are much cooler ways out there, such as dbatools.io/functions/repair-dbaorphanuser/, but
I have occasionally seen these not work in odd situations.  Plus, if your login names and user names
don't exactly match, this method could be extended to enter custom mappings-- you'd simply add a new
column to #HelpUser, say [NewLoginName], and use that in the 'fix-it' statement-generator query.
*/
USE MyDatabase;

NJohnson9402

CREATE OR ALTER TRIGGER dbo.Victims_DEL
    ON dbo.Victims
    AFTER DELETE
AS
BEGIN
    INSERT INTO aud.Evidence (SchemaName, TableName, ActionType, RecordID
        , DatabaseUser, ServerLogin, ActionTime)
    SELECT SchemName = 'dbo', TableName = 'Victims', ActionType = 'delete', RecordID = Deleted.Id
        , DbUser = USER_NAME(), SrvLogin = SUSER_SNAME(), ActionTime = SYSDATETIME()
    FROM Deleted;

NJohnson9402

DECLARE @SummaryView BIT = 0
DECLARE @GenFileRenames BIT = 0
DECLARE @IgnoreSpecials BIT = 0

--Only w/ @SummaryView = 0 && @GenFileRenames = 0
DECLARE @LimitBySize bit = 0
DECLARE @MinSizeGB decimal(10,2) = 5.0
DECLARE @MinPctFree decimal(10,2) = 10.0

--Customized view using GB answer w/ SO temp-table

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

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
)