SDU Tools

Free tools for developers and DBAs

Free for anyone on the SDUInsiders email list. Current release is v19.
SDU Tools is an easy to use toolkit. You just run a single script to install it in any database. Each release completely replaces all contents of the SDU_Tools schema. It contains utility functions, procedures, and views. Many of the functions are useful when performing troubleshooting but others are just general purpose functions. This toolkit is updated roughly once per month.

Find the latest version or sign up to be notified of updates right here, and for suggestions or bug reports, please email sdutools @ sqldownunder.com.

To see how any of the tools work, click the name or description of the tool below for a short video presentation.

Tools Information


Overview

Watch an overview of the tools

Installation

Watch a video that shows how to install the tools (it's easy)

Removal

Watch a video that shows how to remove the tools (it's also easy)

SDUToolsVersion

Returns the version of SDU Tools that is installed

Strings


AlphabeticOnly

Removes any non-alphabetic characters from a string

AlphanumericOnly

Removes any non-alphanumeric characters from a string

AsciiOnly

Removes non-ASCII characters from a string

CamelCase

Converts a string to camelCase

CountWords

Counts the number of words in a string

DigitsOnly

Removes non-digit characters from a string

ExtractTrigrams
Extracts trigrams (segments of up to 3 characters) from a string for fast searching

ExtractTrimmedWords

Extracts words from a string and trims them

InitialsFromName

Extracts a person's initials from their name

InvertString

Returns an upside down version of the string

KebabCase

Converts a string to kebab-case

LeftPad

Left pads a string

PascalCase

Converts a string to PascalCase

PreviousNonWhitespaceCharacter
Finds the previous non-whitespace character working backwards from the current position

ProperCase

Converts a string to Proper Case

QuoteString

Quotes a string

RightPad

Right pads a string

ScreamingSnakeCase

Converts a string to SCREAMING_SNAKE_CASE

SeparateByCase

Inserts spaces in Pascal-cased or camel-cased strings

SingleSpaceWords

Trims a string and replaces multiple spaces with single spaces

SnakeCase

Converts a string to snake_case

SpongeBobSnakeCase

Converts a string to sPoNgEbOb_sNaKeCaSe

StringLength

Returns the length of a string (Properly)

StripDiacritics

Strips diacritics (accents, graves, etc.) from a string

TitleCase

Converts a string to Title Case

TrainCase

Converts a string to Train-Case

Translate

Replaces a set of characters in a string

TrimWhitespace
Removes any leading or trailing space, tab, carriage return, and linefeed characters

Dates and Times


AddWeekdays

Adds a number of weekdays to a date

ChineseNewYearAnimalName

Determines the Chinese zodiac animal name for a particular year

DateDiffNoWeekend
Calculates the number of days between two dates excluding weekends (Saturday and Sunday)

DateOfChineseNewYear

Works out when Chinese New Year occurs in a particular year

DateOfEasterSunday
Works out when Easter Sunday (Christian Easter) occurs in a particular year

DatesBetweenNoWeekends

Calculates the dates in a period excluding weekends

DatesInPeriod

Calculates dates during a number of periods

DayNumberOfMonth

Calculates a specific day within a month (i.e. 2nd Tuesday)

DaysInMonth

Calculates the number of days in a month selected by date

EndOfFinancialYear

Calculates the date for the end of the financial year

EndOfMonth

Calculates the date for the end of a month

EndOfWeek

Calculates the date for the end of a week

EndOfWorkingWeek

Calculates the date for the end of a working week

EndOfYear

Calculates the date for the end of a year

IsLeapYear

Determines if a year is a leap year

IsWeekday

Determines if a date is a weekday

IsWeekend

Determines if a date is a weekend

StartOfFinancialYear

Calculates the date for the start of the financial year

StartOfMonth

Calculates the date for the start of a month

StartOfWeek

Calculates the date for the start of a week

StartOfWorkingWeek

Calculates the date for the start of a working week

StartOfYear

Calculates the date for the start of a year

WeekdayOfMonth

Calculates a specific weekday within a month

CSVs and TSVs


ExtractToken

Extracts a particular token from a delimited (i.e. CSV) string

NumberOfTokens

Counts the number of tokens in a delimited (i.e. CSV) string

ReadCSVFile

Reads columns from a CSV file

SplitDelimitedString

Splits a delimited string (usually either a CSV or TSV) into rows

SplitDelimitedStringIntoColumns

Splits a delimited string (usually either a CSV or TSV) into columns

Data Warehouses


DateDimensionColumns

Table valued function to return date dimension columns for a date

DateDimensionPeriodColumns

Table valued function to return date dimension period columns for a date

DatesBetween

Table valued function to return all dates between two dates

GetDateDimension

Gets all date dimension columns for a range of dates

GetTimePeriodDimension

Gets all time period dimension columns for a day

TimePeriodDimensionColumns

Table valued function to return time period dimension columns for a time

TimePeriodsBetween

Table valued function to return all time periods between two times

Converting and Encoding


Base64ToVarbinary

Converts a base 64 value to varbinary

CharToHexadecimal

Converts a character to a hexadecimal string

DateTime2ToUnixTime

Converts a datetime2 value to a Unix time

DateToJulianDayNumber

Converts a date to a Julian Day

HexCharStringToChar

Converts a two char hex string to an ASCII character

HexCharStringToInt

Converts a two char hex string to an int

IsIPv4Address

Determines if a string is an version 4 IP address

JulianDayNumberToDate

Converts a Julian Day to a date

NullIfBlank

Tests a string and returns NULL if it is blank, otherwise trims it

NullIfZero

Tests a number and returns NULL if it is zero, otherwise the number

NumberAsText

Converts a number to English text

NumberToRomanNumerals

Converts a number to Roman Numerals

PercentEncode

Encodes reserved characters that are used in HTML or URL encoding

ProductVersionComponents

Lists all components of a product version

ProductVersionToBuild

Converts a product version to a build number

ProductVersionToMajorVersion

Converts a product version to a major version

ProductVersionToMinorVersion

Converts a product version to a minor version

ProductVersionToRelease

Converts a product version to a release number

ProductVersionComponents

Lists all components of a product version

ROT13

Implements ROT13 encodind and decoding

SecondsToDuration

Converts a number of seconds to a duration string

SQLVariantInfo

Returns information about a sql_variant value

TimezoneOffsetToHours

Converts a timezone offset value to decimal hours

TruncateTrailingZeroes

Converts a decimal to a string and removes any trailing zeroes

UnixTimeToDateTime2

Converts a Unix time value to a datetime2

VarbinaryToBase64

Converts a varbinary value to base 64 encoding

XMLDecodeString

Decodes an XML encoded string

XMLEncodeString

Performs XML encoding on a string

Performance Tuning


AnalyzePerformanceTuningTrace

Analyzes the queries that are found in a loaded trace file.

CapturePerformanceTuningTrace

Captures a trace needed when carrying out performance tuning.

CheckInstantFileInitializationState

Checks if IFI is enabled or not

DeExecuteSQLString
Assists with debugging and performance troubleshooting of sp_executesql commands, particularly those captured in Profiler or Extended Events traces. Takes a valid exec sp_executesql string and extracts the embedded command from within it. Optionally, can extract the parameters and either embed them directly back into the code, or create them as variable declarations

ExtractSQLTemplate
Used to normalize a SQL Server command, mostly for helping with performance tuning work. Extracts the underlying template of the command. If the command includes an exec sp_executesql statement, tries to undo that statement as well. (Cannot do so if that isn't the last statement in the batch being processed)

FindSubsetIndexes
Finds indexes that appear to be subsets of other indexes in all databases or selected databases

LastParameterStartPosition
Starting at the end of the string, finds the last location where a parameter is defined, based on a @ prefix

ListNonIndexedForeignKeys

Lists foreign keys not supported by indexes

ListUnusedIndexes

Lists unused indexes for a database

ListUserHeapTables

Lists user tables that are heaps (no clustered index)

ListUserTableAndIndexSizes

Lists row counts and sizes for user tables and indexes

ListUserTableSizes

Lists row counts and sizes for user tables

LoadPerformanceTuningTrace

Loads a trace file into a table

ShowCurrentBlocking
Lists sessions (and their last queries) for all sessions holding locks, then lists blocked sessions, with the queries they are trying to execute, and which sessions are blocking them

UpdateStatistics
Updates statistics on a set of tables and or schemas or a complete database

Reference Data


ChineseYears

Lists Chinese New Years, animals, and characters

Countries

Lists all countries and their codes

Currencies

Lists all common currencies and their symbols

CurrenciesByCountry

Lists which currencies specific countries use

FutureReservedWords

Lists T-SQL future reserved words

LoginTypes

Lists available login types

ODBCReservedWords

Lists ODBC reserved words

OperatingSystemLocales

Lists all operating system locales

OperatingSystemSKUs

Lists all operating system SKUs

OperatingSystemVersions

Lists all operating system versions

ReservedWords

Lists T-SQL reserved words

SQLServerProductVersions

Lists all versions of SQL Server

SystemDataTypeNames

Lists system data type names

SystemWords

Lists system reserved words

UserTypes

Lists available user types

Reporting Services


RSCatalogTypes

Details available types in the Reporting Services catalog

RSListContentItems

Lists all content items on the server

RSListUserAccess

Lists which users are in which roles

RSListUserAccessToContent

Lists which users can access which content

Security


CreateSQLLoginWithSIDFromDB

Creates a SQL Login using the SID from a user database

FindStringWithinADatabase
Finds a string anywhere within a database. Can be useful for testing masking of sensitive data. Checks all string type columns and XML columns

Database Quality


AnalyzeTableColumns
Provides metadata for a table's columns and a sample of distinct column values

CalculateTableLoadingOrder

Follows foreign keys to work out the order tables need to be loaded in

LatestSQLServerBuilds

Lists the latest builds for each SQL Server version

ListAllDataTypesInUse
A distinct list of each data type (and size) used with the selected database

ListColumnsAndDataTypes

Lists all columns and their data types for a database

ListEmptyUserTables

Lists tables that have no rows

ListForeignKeyColumns

Lists foreign keys and their columns as rows

ListForeignKeys

Lists foreign keys and their columns as column sets

ListIncomingForeignKeys
Lists foreign keys and their columns filtered by the referenced schema and or table

ListIndexes

Lists indexes with key column lists and include column lists

ListMismatchedDatabaseCollations

List databases with collations that do not match the server

ListMismatchedDataTypes

List columns with the same name that are defined differently

ListPotentialDateColumns

List columns named as dates but actually datetime or datetime2

ListPotentialDateColumnsByValue

List columns defined as dates but not containing time values

ListPrimaryKeyColumns

Lists the columns that make up primary keys for tables

ListUseOfDeprecatedDataTypes
Lists all columns and their data types for a database where the data type is deprecated

ListUserTablesWithNoPrimaryKey

Lists user tables with no primary keys

OperatingSystemConfiguration

Lists the configuration of the current operating system

RetrustCheckConstraints

Retrusts untrusted check constraints

RetrustForeignKeys

Retrusts untrusted foreign keys
ServerMaximumDBCompatibility
Level

Returns the maximum DB compatibility level supported by the server

SetAnsiNullsOnForTable

Changes a table defined with ANSI NULLS off to a table with it on
SetAnsiNullsQuotedIdentifierFor
StoredProcedures

Fixes ANSI NULLS and QUOTED IDENTIFIER for stored procedures
SetDatabaseCompabilityForAll
DatabasesToMaximum

Sets the DB compatibility for all DBs to the maximum supported
SystemConfigurationOption
Defaults

Lists default values for system configuration options

Agent Jobs


ExecuteJobAndWaitForCompletion

Executes a SQL Server Agent job synchronously (waits for it to complete)

IsJobRunning

Determines if a SQL Server Agent job is currently running

Scripting


ExecuteOrPrint
Either prints the generated code or executes it batch by batch. (Unless specified, it assumes that GO is the batch separator)

FormatDataTypeName
Converts data type, maximum length, precision, and scale into the standard format used in scripts

PGObjectName
Converts SQL Server camel-cased or Pascal-cased object names to a name suitable for PostgreSQL conversion

ScriptDatabaseObjectPermissions

Scripts permissions for database objects

ScriptDatabaseUsers

Scripts all users associated with a login for a particular database

ScriptServerPermissions
Scripts all server permissions (apart from CONNECT SQL) for all or selected logins

ScriptServerRoleMembers

Scripts all server role membership for all or selected logins

ScriptSQLLogins
Scripts (all or selected) SQL Logins along with security ID default database, password hash, default language, and policy

ScriptTable

Scripts a SQL Server table with many configuration options

ScriptTableAsUnpivot

Scripts a SQL Server table as an UNPIVOT statement or view
ScriptUserDefinedDatabaseRole
Permissions

Scripts all permissions for user-defined database roles

ScriptUserDefinedDatabaseRoles

Scripts all user-defined database roles
ScriptUserDefinedServerRole
Permissions

Scripts all permissions for user-defined server roles
ScriptUserDefinedDatabaseRole
Permissions

Scripts all permissions for user-defined database roles

ScriptUserDefinedServerRoles

Scripts all user-defined server roles

ScriptWindowsLogins
Scripts (all or selected) Windows Logins along with default database and default language

General Purpose

ClearServiceBrokerTransmission
Queue

Clears service broker transmission queue entries and conversations
CreateLinkedServerToAzureSQL
Database

Creates a linked server to Azure SQL Database

CurrentSessionDecimalSeparator

Returns the decimal separator for the current session
CurrentSessionThousands
Separator

Returns the thousands separator for the current session

DropTemporaryTableIfExists

Drops a temporary table if it exists

EmptySchema

Removes all user objects from the selected schema

EmptySchemaInCurrentDatabase
Removes all user objects from the selected schema in the current database (useful for Azure)

ExecuteCommandInEachDB

Executes a command in each online database

IsXActAbortON

Determines if XACT_ABORT is currently on

PrintMessage

Prints a message immediately (unlike PRINT)
ReseedSequenceBeyondTable
Values
Sets the next value for a sequence past values already used in tables that have this as a column default

ReseedSequences
Applies the sequence reseeding operation to all (or a set of) sequences in a database

ShowBackupCompletionEstimates
Show the status of currently-executing backups and estimate the completion time

Sleep

Sleeps for a number of seconds

SQLServerVersion

Returns the version of SQL Server being used
SQLServerVersionForCompatibilityLevel
Determines a SQL Server version number for a database or server compatibility level

TableOfNumbers
Table valued function that provides a sequence of integers of a defined length, starting at a specific value

Database and Table Comparison Tools


GetDBSchemaCoreComparison

Checks the schema of two databases, looking for basic differences

GetTableSchemaComparison

Checks the schema of two tables, looking for basic differences

Limitations

Note that string manipulation in T-SQL is relatively slow. Many of these functions would be much faster as SQLCLR based implementations but not all systems have SQLCLR integration enabled. To make them work wherever possible, all of these functions are written in pure T-SQL. We also don't assume that you're using the latest version of SQL Server, so the functions are written to work on all currently-supported versions.

The Fine Print (Disclaimer and License)

We try our hardest to make these tools as useful and bug free as possible, but like any software, we can never guarantee that there won't be any issues. We hope you'll decide to use the tools but all liability for using them is with you, not us. You are free to use the tools in this collection as long as you keep them in the SDU_Tools schema as a single set of tools, and as long as this notice is kept in any script file copies of the tools. We don't want lots of different versions of them out in the wild and would prefer them to stay in a single small schema that's easy to upgrade. They can be used privately or commercially. You may not redistribute, or resell them, but you can use them as examples of how to write the different types of functions.
Free for anyone on the SDUInsiders email list. Current release is v19.