Skip to content

Instantly share code, notes, and snippets.

Avatar
💭
Presenting at #SQLSatOC

Bill Fellows billinkc

💭
Presenting at #SQLSatOC
  • Sterling Data Consulting
  • Kansas City, MO
View GitHub Profile
@billinkc
billinkc / InstallLogparser.ps1
Last active Sep 8, 2021
Assuming you wanted to grab the info from a SQL Server install log - specifically the Exit Code, here's some terrible PowerShell to do so
View InstallLogparser.ps1
# Sample data of what Summary.txt looks like
<#
Overall summary:
Final result: Passed
Exit code (Decimal): 0
Start time: 2021-02-26 03:03:25
End time: 2021-02-26 03:04:36
Requested action: Patch
Instance DEV2019UTF8 overall summary:
@billinkc
billinkc / BadIdeaJeans_TemporalTableEdition.sql
Created Aug 18, 2021
Bad ideas for cheating with temporal tables
View BadIdeaJeans_TemporalTableEdition.sql
USE tempdb
IF EXISTS (SELECT * FROM sys.schemas AS S INNER JOIN sys.tables AS T ON T.schema_id = S.schema_id WHERE S.name = 'dbo' AND T.name = 'Employee')
BEGIN
BEGIN TRY
ALTER TABLE dbo.Employee
SET (SYSTEM_VERSIONING = OFF);
END TRY
BEGIN CATCH
-- I don't care if system versioning is on
@billinkc
billinkc / DelimitedLists.cs
Last active Feb 20, 2020
Code to accompany a blog post about building delimited lists in .net
View DelimitedLists.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Xml.Linq;
using System.Text;
public class Program
{
public static void Main()
{
@billinkc
billinkc / JsonShredder.biml
Created Sep 5, 2019
How to use a json metadata store with Biml
View JsonShredder.biml
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<#
// Given the following structure
///{
/// "packages": [
/// "p1",
/// "p2",
/// "p3"
/// ]
///}
@billinkc
billinkc / Rush.sql
Created May 21, 2019
Find non-alphanumerics in sql
View Rush.sql
IF OBJECT_ID('tempdb..#Numbers') IS NOT NULL
BEGIN
DROP TABLE #Numbers;
END
IF OBJECT_ID('tempdb..#rush') IS NOT NULL
BEGIN
DROP TABLE #rush;
END
@billinkc
billinkc / BoomGoesTheDynamite.sql
Last active Nov 29, 2018
Microsoft SQL Server 2014/2016/2017 index repro
View BoomGoesTheDynamite.sql
IF NOT EXISTS
(
SELECT * FROM sys.partition_functions AS PF WHERE PF.name = 'PartitionRange'
)
BEGIN
CREATE PARTITION FUNCTION PartitionRange(bigint) AS RANGE RIGHT FOR VALUES (0, 1, 2, 3, 4, 5, 6, 7, 8, 9);
END
GO
IF NOT EXISTS
(
@billinkc
billinkc / TableViaQuery.biml
Created Sep 4, 2017
Given a query, generate the resulting Biml table
View TableViaQuery.biml
<#@ import namespace="System.Data" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<#
Dictionary<string, string> namedQueries = new Dictionary<string,string>{{"Query 28", @"-- Drive level latency information (Query 28) (Drive Level Latency)
-- Based on code from Jimmy May
SELECT tab.[Drive], tab.volume_mount_point AS [Volume Mount Point],
CASE
WHEN num_of_reads = 0 THEN 0
ELSE (io_stall_read_ms/num_of_reads)
END AS [Read Latency],
@billinkc
billinkc / JsonPackUnpack.sql
Last active Jan 13, 2017
Testing JSON with SQL Server 2016 and I'm confused on the seemingly extraneous square bracket around the child table
View JsonPackUnpack.sql
SET NOCOUNT ON;
IF OBJECT_ID('tempdb..#Sales') IS NULL
BEGIN
CREATE TABLE #Sales
(
RecordKey int NOT NULL
, SalesDate date NOT NULL
, CONSTRAINT PK_tmp_Sales PRIMARY KEY (RecordKey)
)
INSERT INTO
@billinkc
billinkc / DoubleMetaphone.sql
Created Aug 24, 2016
Tired of looking through my script library for a double metaphone implmentation
View DoubleMetaphone.sql
-----------------------------------------------------------------------------
IF EXISTS
(
SELECT
*
FROM
dbo.sysobjects
WHERE
id = OBJECT_ID('dbo.DoubleMetaPhone')
AND OBJECTPROPERTY(id, 'IsDeterministic') IN (1, 0)
@billinkc
billinkc / ExportTables.biml
Created Aug 2, 2016
Biml Export table definitions. This script uses the extension methods GetDatabaseSchema and GetDropAndCreateDdl to reverse engineer a database model into memory and then emit sql files with the proper drop and create table scripts. For more info, see http://www.cathrinewilhelmsen.net/2015/07/12/biml-extension-methods-getdatabaseschema/
View ExportTables.biml
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<#@ template tier="1" #>
<#@ import namespace="Varigence.Biml.CoreLowerer.SchemaManagement" #>
<#@ import namespace="System.IO" #>
<#
var schema = new List<string>{"CMS"};
var ODSCM = RootNode.OleDbConnections["ODS"];
var ODSDB = ODSCM.GetDatabaseSchema(schema, null, ImportOptions.None);
string fileNameTemplate = @"C:\Users\fellowsb\Documents\ODSDB\{0}_{1}.sql";
string currentFileName = string.Empty;