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
