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
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.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
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 https://natethedba.wordpress.com/2019/05/14/t-sql-tuesday-114-a-puzzle/ .
View DemoCribbageHandScorer.sql
--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
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 natethedba.wordpress.com/quickie-read-sql-error-log-in-aws-rds .
View FetchAndReadSqlErrorLogs.sql
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
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 https://natethedba.wordpress.com/tag/tsql2sday/ . 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
NJohnson9402 / GetSqlReplicationArticles.sql
Created Jul 11, 2018
SQL Server- get replication articles from a given published database. See https://natethedba.wordpress.com/tag/tsql2sday/. 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]
LEFT JOIN (
SELECT 'table' AS [type], name
@NJohnson9402
NJohnson9402 / GetDatabaseFilesAndSpaceUsed.sql
Created Jul 11, 2018
SQL Server- get all database files and space used on an instance. See https://natethedba.wordpress.com/tag/tsql2sday/ . 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
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;
View OrphanedUsersFixByName.sql
/* 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
NJohnson9402 / RenameDatabaseByBackupDetachAttach.sql
Created Dec 2, 2017
See corresponding blog post at http:/natethedba.wordpress.com/adventures-in-database-renaming .
View RenameDatabaseByBackupDetachAttach.sql
/* 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;
View FindTriggerDependencies_AuditTrailExample.sql
/* 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
You can’t perform that action at this time.