SDU Tools

Free tools for developers and DBAs

Free for anyone on the SDUInsiders email list. Current release is v24.
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

Tools

Allows querying all the installed tools

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

CobolCase

Converts a string to COBOL-CASE

CountWords

Counts the number of words in a string

DigitsOnly

Removes non-digit characters from a string

ExtractToken

Extracts a specific token number from a delimited string like a CSV or TSV

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

ExtractTrimmedWords

Extracts words from a string and trims them

FormatAustralianPhoneNumber

Formats a phone number in Australian formats

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

NullIfBlank

Returns NULL if a string is blank or trims the string

PascalCase

Converts a string to PascalCase

PercentEncode

Encodes reserved characters that are used in HTML or URL encoding

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

SplitDelimitedString

Splits a delimited string (often CSV or TSV)

SplitDelimitedStringIntoColumns

Splits a delimited string (often CSV or TSV) into separate columns

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

XMLDecodeString
XML decodes a string

XMLEncodeString
XML encodes a string

Dates and Times


AddWeekdays

Adds a number of weekdays to a date

CalculateAge

Calculates the age of anything based upon a target date

CalculateAgeInMonths

Calculates the age of anything in months based upon a target date

ChineseNewYearAnimalName

Calculates the zodiac animal name for a given year

ChineseYears

View that returns details about Chinese years

DateDiffNoSundays

Determines the number of days between two dates, excluding Sundays

DateDiffNoWeekends

Determines the number of days between two dates, excluding weekends

DateDimensionColumns

Returns columns often used in data warehouse date dimensions

DateDimensionPeriodColumns

Returns columns often used in date warehouse period dimensions

DateOfChineseNewYear

Calculates the date of Chinese New Year for a given year

DateOfEasterSunday

Calculates the date of (Christian) Easter Sunday for a given year

DateOfOrthodoxEaster

Calculates the date of (Orthodox) Easter Sunday for a given year

DatesBetween

Function that returns the list of dates between two dates

DatesBetweenNoWeekends

Function that returns the list of dates between two dates excluding weekends

DatesInPeriod

Function that returns the list of dates for a number of periods

DateTime2ToUnixTime

Converts datetime2 values to Unix time

DateTimeToExcelSerial

Converts SQL Server datetime values to Excel datetime (serial)

DateToJulianDayNumber

Converts a date to a Julian day number

DayNumberOfMonth

Returns the nth instance of a specific day for a month

DaysInMonth

Returns the number of days in a specific month

EndOfFinancialYear

Calculates the end of a financial year date based upon a starting month

EndOfMonth

Calculates the end of a month

EndOfWeek

Calculates the end of a week

EndOfWorkingWeek

Calculates the end of a working week

EndOfYear

Calculates the end of a year

ExcelSerialToDateTime

Converts an Excel datetime (serial) value to a SQL Server datetime

GetDateDimension

Returns a data warehouse date dimension for a specified range of dates

GetTimePeriodDimension

Returns a data warehouse time dimension for a given day

IsLeapYear

Determines if a year is a leap year

IsWeekday

Determines if a day is a weekday

IsWeekend

Determines if a day is a weekend

JulianDayNumberToDate

Converts a Julian day number to a date

NearestWeekday

Calculates the nearest specified day of the week to a date

StartOfFinancialYear

Calculates the start of the financial year for a specified starting month

StartOfMonth

Calculates the start of a month

StartOfWeek

Calculates the start of a week

StartOfWorkingWeek

Calculates the start of a working week

StartOfYear

Calculates the start of a year

TimePeriodDimensionColumns

Returns the columns often used in a data warehouse time dimension

TimePeriodsBetween

Returns a list of time periods of a specified size between two times

TimezoneOffsetToHours

Converts a timezoneoffset to a decimal number of hours

UnixTimeToDateTime2

Converts a Unix time to a datetime2 value

WeekdayAcrossYears

Returns the name of the day of the week for a given day and month across a range of years

WeekdayOfMonth

Returns the nth weekday of a month (e.g. 2nd Tuesday)

WeekdayOfSameWeek

Returns the specified weekday for a given week

Data Conversion Functions


Base64ToVarbinary

Converts a base 64 value to varbinary

CharToHexadecimal

Converts a character to a hexadecimal string

CurrentSessionDecimalSeparator

Determines the decimal separator for the current session

CurrentSessionThousandsSeparator

Determines the thousands separator for the current session

FilePathComponents

Extracts the components of a file path

FilePathToFileExtension

Extracts the file extension from a file path

FilePathToFileName

Extracts the file name from a file path

FilePathToFolderName

Extracts the folder name from a file path

HexCharStringToChar

Converts a hexadecimal string to a character

HexCharStringToInt

Converts a hexadecimal string to an int

NumberAsText

Converts a number to English words (like appeared on checks/cheques)

NumberToRomanNumerals

Converts a number to Roman numerals as a string

ROT13

Applies ROT13 encoding/decoding of a string

SecondsToDuration

Converts a number of seconds to a SQL Server duration string

SQLServerVersionForCompatibilityLevel

Converts a SQL Server database compatibility level to a version

SQLVariantInfo

Returns information about a sql_variant value

TruncateTrailingZeroes

Converts a decimal value to a string without trailing zeroes

URLComponents

Extracts components of a URL

URLToAnchor

Extracts an anchor from a URL

URLToHostName

Extracts a host name from a URL

URLToPathName

Extracts a path from a URL

URLToProtocol

Extracts a protocol from a URL

URLToSearchTerms

Extracts all search terms from a URL

VarbinaryToBase64

Converts a varbinary value to base 64

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

General Purpose Functions


IsIPv4Address

Determines if a string has a valid IPv4 address format

ListInstalledSQLServerInstances

Finds all installed SQL Server instances, types, and versions on a server

NULLIfZero

Returns NULL if the input value is zero otherwise decimal(18, 2) output

ProductVersionComponents

Extracts the components of a product version

ProductVersionToBuild

Extracts the build from a build number (product version)

ProductVersionToMajorVersion

Extracts a product major version from a build number (product version)

ProductVersionToMinorVersion

Extracts a product minor version from a build number (product version)

ProductVersionToRelease

Extracts a product release from a build number (product version)

SQLServerType

Determines what type of server the commands are being executed against

SQLServerVersion

Returns the SQL Server version as a string

SDUToolsVersion

Returns the version of SDU Tools

TableOfNumbers

Returns a specified set of numbers from a starting value

Database Utilities


AnalyzeTableColumns

Metadata and distinct values for columns in a table

CalculateTableLoadingOrder

Determines the order to load tables based upon foreign keys

CheckInstantFileInitializationState

Determines if IFI is enabled

ClearServiceBrokerTransmissionQueue

Clears the contents of the transmission queue for service broker

CreateLinkedServerToAzureSQLDatabase

Creates a linked server to an Azure SQL Database

CreateSQLLoginWithSIDFromDB

Avoids SID mismatch problems by creating a login with the SID taken from a database

DropTemporaryTableIfExists

Drops a temporary table if it exists

EmptySchema

Removes all objects from within a schema

EmptySchemaInCurrentDatabase

Removes all objects from within a schema in the current database (useful for Azure SQL Database)

ExecuteCommandInEachDB

Executes a command in each database

ExecuteJobAndWaitForCompletion

Executes a SQL Agent job and waits for it to complete

FindStringWithinADatabase

Searches for any occurrence of a string within a database (useful for checking masking)

IsJobRunning

Checks if a SQL Agent job is currently running

IsLockedPagesInMemoryEnabled

Checks if the Lock Pages in Memory setting is enabled

IsXActAbortOn

Checks if XACT_ABORT is currently in the session

LastSQLServerRestart

Finds out when SQL Server was last restarted

ListAllDataTypesInUse

Lists all data types in use in the database

ListColumnsAndDataTypes

Lists all columns and their data types for a database

ListConstraintsWithSystemNames

Lists all constraints that were not intentionally named

ListDisabledIndexes

Lists disabled indexes

ListEmptyUserTables

Lists user tables that are empty

ListForeignKeyColumns

Lists foreign keys and their columns

ListForeignKeys

Lists foreign keys

ListIncomingForeignKeys

Lists incoming foreign keys (targets of foreign keys)

ListIndexes

Lists indexes

ListMismatchedDatabaseCollations

Lists databases with collations different to the server

ListMismatchedDataTypes

Lists columns with the same name that are defined differently across a database

ListNonIndexedForeignKeys

Lists foreign keys not supported by an index

ListPotentialDateColumns

Lists columns names as dates but not date data type

ListPotentialDateColumnsByValue

Lists columns declared as having date and time values but only holding dates

ListPrimaryKeyColumns

Lists primary keys and their columns

ListSubsetIndexes

Lists indexes that are subsets (or duplicates) of other indexes

ListUntrustedCheckConstraints

Lists check constraints that are not trusted

ListUntrustedForeignKeys

Lists foreign key constraints that are not trusted

ListUnusedIndexes

Lists indexes declared but not used

ListUseOfDeprecatedDataTypes

Lists columns declared with deprecated data types

ListUserDefinedDataTypes

Lists user defined (alias) data types and underlying system types

ListUserHeapTables

Lists user tables with no clustering key

ListUserTableAndIndexSizes

Lists the size of tables and their indexes

ListUserTableSizes

Lists the size of user tables

ListUserTablesWithNoPrimaryKey

Lists user tables that have no declared primary key

ListViewColumnsAndDataTypes

Lists columns, data types, and system data types for columns in views

PrintMessage

Print a message without delay

ReadCSVFile

Reads CSV or TSV file with up to 50 columns

ReseedSequenceBeyondTableValues

Finds the highest value used by a sequence in any table and sets its next value beyond that

ResetSequence

Resets a sequence either to one or to a specific value

RetrustCheckConstraints

Retrusts check constraints that are flagged as untrusted

RetrustForeignKeys

Retrusts foreign keys that are flagged as untrusted

ServerMaximumDBCompatibilityLevel

Determines the maximum DB compatibilty level for a server

SetAnsiNullsOnForTable

Fix the Ansi Nulls setting for a table created with it off

SetAnsiNullsQuotedIdentifierForStoredProcedures

Set Ansi Nulls and quoted identifier settings for stored procedures

SetDatabaseCompatibilityLevelForAllDatabases

Sets the database compatibility level for all databases

ShowBackupCompletionEstimates

Estimate when a backup will complete

ShowCurrentBlocking

Show blocking currently occurring in the database

Sleep

Sleep for a number of seconds (non-blocking)

UpdateStatistics

Update statistics within a database

Reporting Services Functions


RSCatalogTypes

Lists different types of catalog objects

RSListContentItems

Lists all items stored in RS along with their types and paths

RSListUserAccess

Lists RS users and their roles

RSListUserAccessToContent

Lists RS users and their access to content

Scripting Functions


ExecuteOrPrint

Executes and/or prints SQL Server code (deals with longer values than PRINT does)

FormatDataTypeName

Converts a data type, maximum length, precision, and scale to the standard format

PGObjectName

Converts a Pascal-cased or camel-cased SQL Server object name to one that could be useful in other databases like PostgreSQL

ScriptAnalyticsView

Scripts an analytic view for a datawarehouse table

ScriptDatabaseObjectPermissions

Scripts object permissions for a database

ScriptDatabaseUsers

Scripts users in a database

ScriptServerPermissions

Scripts object permissions for a SQL Server

ScriptServerRoleMembers

Scripts role members for server roles

ScriptSQLLogins

Scripts SQL logins along with security IDs (SIDs)

ScriptTable

Scripts a table with many options, including alternate name and schema

ScriptTableAsUnpivot

Scripts a table as an unpivoted query or view

ScriptUserDefinedDatabaseRolePermissions

Scripts user defined database role permissions

ScriptUserDefinedDatabaseRoles

Scripts user defined database roles

ScriptUserDefinedServerRolePermissions

Scripts user defined server role permissions

ScriptUserDefinedServerRoles

Scripts user defined server roles

ScriptWindowsLogins

Scripts Windows logins

Performance Tuning Functions and Procedures


AnalyzePerformanceTuningTrace

Analyzes the queries found in a loaded trace file

CapturePerformanceTuningTrace

Capture a performance tuning trace file

DeExecuteSQLString

Converts an sp_executesql string to a query

ExtractSQLTemplate

Extracts a normalized SQL query from a parameterized query

LastParameterStartPosition

Helper function for finding parameters in an sp_executesql query

LoadPerformanceTuningTrace

Loads a captured performance tuning trace

Utility Views


Countries

List of the world's countries

Currencies

List of the world's currencies

CurrenciesByCountry

List of currencies used by each country

FutureReservedWords

List of SQL Server future reserved words

Languages

List of the world's languages

LatestSQLServerBuilds

List of the latest builds for each version of SQL Server

LoginTypes

List of SQL Server login types

NonDefaultSystemConfigurationOptions

List of SQL Server configurations not currently set to default values

ODBCReservedWords

List of ODBC reserved words

OperatingSystemConfiguration

Lists the configuration of the current operating system

OperatingSystemLocales

List of operating system locales

OperatingSystemSKUs

List of operating system SKUs

OperatingSystemVersions

List of operating system versions

ReservedWords

List of SQL Server reserved words

SQLServerProductVersions

List of SQL Server product versions and builds

SystemConfigurationOptionDefaults

List of SQL Server system configuration default values

SystemDataTypeNames

List of SQL Server system data type names

SystemWords

List of SQL Server reserved words and future reserved words

UserTypes

List of SQL Server user types