Skip to content

Instantly share code, notes, and snippets.

Nate Johnson NJohnson9402

Block or report user

Report or block NJohnson9402

Hide content and notifications from this user.

Learn more about blocking users

Contact Support about this user’s behavior.

Learn more about reporting abuse

Report abuse
View GitHub Profile
NJohnson9402 / AtTimeZoneDemo.sql
Created Oct 31, 2019
Demo script for AT TIME ZONE usage with reporting on a transaction table
View AtTimeZoneDemo.sql
/* The system table that contains TZ info
SELECT, 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;
DROP TABLE dbo.MyTrans;
NJohnson9402 / DemoCribbageHandScorer.sql
Last active May 20, 2019
Model a card deck and a cribbage hand (5 cards), and demonstrate how to count all the '15's (combos of cards that add up to 15 by face-value). See .
View DemoCribbageHandScorer.sql
--Put this stuff in a new schema so we can easily clean up afterward.
--Set up our Cards model
--DROP TABLE play.Cards;
, Face varchar(2)
NJohnson9402 / FetchAndReadSqlErrorLogs.sql
Last active Jul 20, 2018
Fetch and read SQL Error Logs on AWS RDS (or any SQL Server, but it's meant for RDS where permissions are limited!). See .
View FetchAndReadSqlErrorLogs.sql
USE master;
DECLARE @CONST_P2 int = 1; --Error log, not Agent log.
--^ (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 / GetSsrsSubscriptionInfoFromReportServer.sql
Created Jul 11, 2018
SQL Server Reporting Services (SSRS)- find Report Subscriptions by Email or Comments or Name/Description, and view last Status (i.e. was it sent?). See . Subject to updates and open to feedback!
View GetSsrsSubscriptionInfoFromReportServer.sql
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 / GetSqlReplicationArticles.sql
Created Jul 11, 2018
SQL Server- get replication articles from a given published database. See Replace [dbName] at top with your published database name. Subject to update and open to feedback!
View GetSqlReplicationArticles.sql
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]
SELECT 'table' AS [type], name
NJohnson9402 / GetDatabaseFilesAndSpaceUsed.sql
Created Jul 11, 2018
SQL Server- get all database files and space used on an instance. See . Subject to updates and open to feedback!
View GetDatabaseFilesAndSpaceUsed.sql
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
View Trigger_AfterDelete_LogEvidence.sql
ON dbo.Victims
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;
View OrphanedUsersFixByName.sql
/* NJohnson9402 /
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, 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 / RenameDatabaseByBackupDetachAttach.sql
Created Dec 2, 2017
See corresponding blog post at http:/ .
View RenameDatabaseByBackupDetachAttach.sql
/* NJohnson9402 /
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;
View FindTriggerDependencies_AuditTrailExample.sql
/* NJohnson9402 /
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] =
You can’t perform that action at this time.