Skip to content

Instantly share code, notes, and snippets.

Avatar

josh sqldeployhelmet

View GitHub Profile
@sqldeployhelmet
sqldeployhelmet / ExtractSQLDocs.ps1
Created Dec 11, 2020
PowerShell script for extracting a Jekyll ready files from SQL Doc markdown files.
View ExtractSQLDocs.ps1
<#
Author: Josh Smith
Created: 2020-09-03
This script will parse through SQL Doc Generated markdown files to create Jekyll friendly
db documentation.
Should be run from the root directory of the documentation repository.
#>
function createHeader { param ($subject, $link, $page)
@sqldeployhelmet
sqldeployhelmet / SQLUserPermissionsQry.sql
Last active Nov 28, 2020
Quick SQL Instance and DB Access Collection
View SQLUserPermissionsQry.sql
/* We collect the results daily and haven't been too concerned
about lost history if a particular instance fails occasionally */
DELETE FROM
dbo.secPoll;
CREATE TABLE ##userQuery
(
serverName VARCHAR(255)
, databaseName VARCHAR(255)
@sqldeployhelmet
sqldeployhelmet / backuplist.json
Last active Mar 4, 2020
Output from the CosmosDB backup list script.
View backuplist.json
[
{
"ContainerName" : "myFirstContainer",
"DatabaseName" : "myFirstDatabase",
"ResourceName" : "cosmos-db-service-1"
},
{
"ContainerName" : "mySecondContainer",
"DatabaseName" : "myFirstDatabase",
"ResourceName" : "cosmos-db-service-1"
@sqldeployhelmet
sqldeployhelmet / ListCosmosDBContainers.ps1
Last active Mar 4, 2020
Powershell script to query cosmosdb services for a list of databases and containers in them.
View ListCosmosDBContainers.ps1
<#
Author: Josh Smith
Created: 2020-03-02
Purpose: Will pull all databases and containers from known CosmosDB resources.
Cosmos db services must be known and added to the array below (paired with the
associated resource group).
#>
<# add cosmos db resource and groups here as needed: #>
@sqldeployhelmet
sqldeployhelmet / TeamCityBuildNumber.ps1
Created Jan 30, 2020
PS Code for Dynamically setting a build number.
View TeamCityBuildNumber.ps1
# The build number format is YYYY.MM.B (Year.Month.BuildCounter)
$MonthCounter = ([DateTime]::Now.Month).ToString().PadLeft(2, "0")
$YearCounter = ([DateTime]::Now.Date.Year).ToString()
$DayCounter = ([DateTime]::Now.Day).ToString().PadLeft(2, "0")
$TimeCounter = ([DateTime]::Now.Hour).ToString().PadLeft(2, "0")
$MinuteCounter = ([DateTIme]::Now.Minute).ToString().PadLeft(2, "0")
$counter = "$YearCounter.$MonthCounter.$DayCounter.$TimeCounter" + $MinuteCounter
@sqldeployhelmet
sqldeployhelmet / cosmosDB.json
Last active Oct 2, 2019
Cosmos DB backup settings
View cosmosDB.json
[
{
"_comment" : ["This is a sample object that should be modified for deployment.",
"Connect strings will need to be inserted and correct service, database and collection",
"names included as well as setting the database backup flag to true as needed."],
"service" : {
"name" : "<name of your cosmos db service>",
"connectString" : "<read-only connect string here>",
"databases" : [ {"name" : "database1",
@sqldeployhelmet
sqldeployhelmet / CosmosDBBkup.ps1
Created Oct 2, 2019
Powershell to backup a CosmosDB
View CosmosDBBkup.ps1
<#
This script will call the cosmos db migration tool with the correct parameters based
on a list of databases that need to be backed up. It depends on a json param file that
contains the list of cosmos db services that have databases that require backup.
This script has a couple of dependencies:
(1) the dt.exe that it runs (the cosmos db migration tool and we assume the associated files/dlls
in the compiled folder) needs to be locally available.
@sqldeployhelmet
sqldeployhelmet / NVARCHARdownsample.sql
Last active Apr 29, 2019
Converting an NVARCHAR to VARCHAR for fun and profit
View NVARCHARdownsample.sql
/* Preface the string with N or
SQL will pre-emptively destroy
the data before storing the string */
DECLARE @nv AS NVARCHAR(10) = N'╥«┘{║‼℈≠'
, @v AS VARCHAR(10);
SELECT @nv, CAST(@nv AS VARCHAR(10));
/* And by reassigning we can see
the data is permanently lost */
View sqlqueryorderby.sql
/* What movies have been nominated for 1, 2 or 3 Oscars? */
SELECT name, releaseDate, oscarNoms
FROM films
/* if any one of the following conditions is true for a row it will be returned */
WHERE oscarNoms = 1
OR oscarNoms = 2
OR oscarNoms = 3;
/* These can more easily be written with the IN clause */
SELECT name releasedDate
@sqldeployhelmet
sqldeployhelmet / sqlquerymathfilters.sql
Last active Apr 7, 2019
Using simple mathematical operators for filters in SQL
View sqlquerymathfilters.sql
/* films with exactly 2 oscar nominations */
SELECT name, releaseDate
FROM films
WHERE oscarNoms = 2;
/* films with more than 4 oscar nominations*/
SELECT name, releaseDate
FROM films
WHERE oscarNoms > 4;