SDU Tools

Free tools for developers and DBAs

Free for anyne 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 every few months.

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

Topic		Description
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

Topic		Description
AlphabeticOnly		Removes all non-alphabetic characters in a string
AlphanumericOnly		Removes all non-alphanumeric characters in a string
AsciiOnly		Removes or replaces all non-ASCII characters in a string
CamelCase		Apply Camel Casing to a string
CobolCase		Apply Cobol Casing to a string
CountWords		Counts words in a string, using English syntax
DigitsOnly		Removes all non-digit characters in a string
ExtractToken		Extracts a specific token from a delimited string
ExtractTrigrams		Extracts all trigrams (up to 3 character substrings) from a string
ExtractTrimmedWords		Returns an ordered table of trimmed words extracted from the string
FormatAustralianPhoneNumber		Formats a string as an Australian phone number
InitialsFromName		Returns the initials from a name, separated by a separator
InvertString		Inverts a string by using USD Encoding as per https://en.wikipedia.org/wiki/Transformation_of_text#Upside-down_text
KebabCase		Apply Kebab Casing to a string
LeftPad		Left pads a string
NumberAsText		Converts a number to a text string (using English words)
NumberOfTokens		Counts the number of tokens in a delimited string (usually either a CSV or TSV)
PascalCase		Apply Pascal Casing to a string
PercentEncode		Apply percent encoding to a string (could be used for URL Encoding)
PreviousNonWhitespaceCharacter		Locates the previous non-whitespace character in a string
ProperCase		Apply Proper Casing to a string
QuoteString		Quotes a string (also doubles embedded quotes)
RightPad		Right pads a string
ROT13		Performs ROT-13 encoding or decoding of a string
ScreamingSnakeCase		Apply Screaming Snake Casing to a string
SeparateByCase		Insert a separator between Pascal cased or Camel cased words
SingleSpaceWords		Removes any whitespace characters and returns words single-spaced
SnakeCase		Apply Snake Casing to a string
SplitDelimitedString		Splits a delimited string (usually either a CSV or TSV)
SplitDelimitedStringIntoColumns		Splits a delimited string into columns (usually either a CSV or TSV)
SpongeBobSnakeCase		Apply SpongeBob Snake Casing to a string
StringLength		Determines the length of a string. Unlike LEN(), does not ignore trailing blanks
StripDiacritics		Strips diacritics (accents, graves, etc.) from a string
TitleCase		Apply Title Casing to a string (similar to book titles)
TrainCase		Apply Train Casing to a string
Translate		Replace a set of characters in a string with a replacement set of characters
TrimWhitespace		Trims all whitespace around a string
TruncateTrailingZeroes		Returns the value converted to a string with trailing zeroes truncated
XMLDecodeString		XML decodes a string
XMLEncodeString		XML encodes a string

Dates and Times

Topic		Description
AddWeekdays		Adds a number of weekdays (non-weekend-days) to a start date
CalculateAge		Return an age in years from a starting date to a calculation date
CalculateAgeInMonths		Return an age in months from a starting date to a calculation date
ChineseNewYearAnimalName		Returns the zodiac animal name for Chinese New Year in a given year
ChineseYears		Maps Years to Chinese Years
DateDiffNoSundays		Determines the number of days (Monday to Saturday) between two dates
DateDiffNoWeekends		Determines the number of days (Monday to Friday) between two dates
DateDimensionColumns		Returns a single row with date dimension columns
DateDimensionPeriodColumns		Returns a single row table with date dimension period columns
DateOfChineseNewYear		Calculates the date of Chinese New Year for a given year
DateOfEasterSunday		Calculates the date of Easter Sunday (Christian Easter) for a given year
DateOfOrthodoxEaster		Returns the date of Orthodox Easter in a given year
DatesBetween		Returns all dates between the two dates supplied (inclusive)
DatesBetweenNoWeekends		Returns a table of dates excluding weekends
DatesFYTD		Returns all dates from the start of the financial year to the date given
DatesInPeriod		Returns a table of dates
DatesYTD		Returns all dates from the start of the calendar year to the date given
DayNumberOfMonth		Returns the nth nominated day of the month
DaysInMonth		Returns the total number of days in the month for a given date
EndOfFinancialYear		Calculates the last date of the financial year for any given date
EndOfMonth		Calculates the last date of the month for any given date
EndOfWeek		Calculates the last date of the week for any given date
EndOfWorkingWeek		Calculates the last date of the working week for any given date
EndOfYear		Calculates the last date of the year for any given date
GetDateDimension		Outputs date dimension columns for all dates in the supplied range of dates
GetTimePeriodDimension		Outputs time period dimension columns for an entire day
IsLeapYear		Determines if a given year is a leap year
IsWeekday		Determines if the provided date is a week day (Monday to Friday)
IsWeekend		Determines if the provided date is a weekend day (Saturday or Sunday)
NearestWeekday		Returns the nominated day closest to the date supplied
StartOfFinancialYear		Calculates the first date of the financial year for any given date
StartOfMonth		Calculates the first date of the month for any given date
StartOfWeek		Calculates the first date of the week for any given date
StartOfWorkingWeek		Calculates the first date of the working week for any given date
StartOfYear		Calculates the first date of the year for any given date
TimePeriodDimensionColumns		Returns a single row of time period dimension columns
TimePeriodsBetween		Returns a table of time periods (start of each time period)
TimezoneOffsetToHours		Calculates a number of hours from a timezone offset
WeekdayAcrossYears		Returns a table of days of the week for a given day over a set of years
WeekdayOfMonth		Returns the nth weekday of the month
WeekdayOfSameWeek		Returns the nominated day in the same week as the target date

Data Conversion Functions

Topic		Description
Base64ToVarbinary		Converts a base 64 value to varbinary
CharToHexadecimal		Converts a single character to a hexadecimal string
DateTime2ToUnixTime		Converts a datetime2 value to Unix time
DateTimeToExcelSerial		Convert a SQL Server datetime to an Excel serial value (datetime)
DateToJulianDayNumber		Converts the date to a Julian day number
ExcelSerialToDateTime		Convert an Excel serial value (datetime) to a SQL Server datetime
FilePathComponents		Extracts components from a file path
FilePathToFileExtension		Extracts a file extension from a file path
FilePathToFileName		Extracts a filename from a file path
FilePathToFolderPath		Extracts a folder path from a file path
HexCharStringToChar		Converts a hexadecimal character string to a character
HexCharStringToInt		Converts a hexadecimal character string to an integer
JulianDayNumberToDate		Converts a Julian day number to a date
NullIfBlank		Returns a NULL string if the string contains no characters, else trims the string
NullIfZero		Returns a NULL decimal if the value is zero
NumberToRomanNumerals		Converts a number to a text string containing Roman Numerals
SecondsToDuration		Convert a number of seconds to a SQL Server duration string
SQLVariantInfo		Returns information about a SQL_variant value
UnixTimeToDateTime2		Converts a Unix time to a datetime2 value
URLComponents		Extracts components from a URL
URLToAnchor		Extracts anchor from a URL
URLToHostName		Extracts host name from a URL
URLToPathName		Extracts path name from a URL
URLToProtocol		Extracts protocol from a URL
URLToSearchTerms		Extracts search terms from a URL
VarbinaryToBase64		Converts a varbinary value to base 64 encoding

Database and Table Comparison Tools

Topic		Description
GetDBSchemaCoreComparison		Checks the schema of two databases, looking for basic differences (user objects only)
GetTableSchemaComparison		Check the schema of two tables, looking for basic differences

Database Utilities

Topic		Description
AnalyzeTableColumns		Provide metadata for a table's columns and list the distinct values held in the column (up to a maximum number)
CalculateTableLoadingOrder		Work out dependency between tables and work out loading order
CheckInstantFileInitializationState		Checks if IFI is enabled (Note: for systems earlier than SQL Server 2014 SP2 the procedure must create and drop a database to test)
ClearServiceBrokerTransmissionQueue		Removes all messages in the service broker transmission queue by ending all existing conversations
CreateLinkedServerToAzureSQLDatabase		Creates a linked server that points to an Azure SQL Database
CreateSQLLoginWithSIDFromDB		Creates a SQL Login with SID retrieved from a database
CurrentSessionDecimalSeparator		Works out what the decimal separator is for the current session
CurrentSessionThousandsSeparator		Works out what the thousands separator is for the current session
DropTemporaryTableIfExists		If the temporary table is defined in the current session, the table is dropped
EmptySchema		Removes objects in the specified schema in the specified database
EmptySchemaInCurrentDatabase		Removes objects in the specified schema in the current database
ExecuteCommandInEachDB		Executes a T-SQL Command in each database
ExecuteJobAndWaitForCompletion		Executes a SQL Server Agent job synchronously (waits for it to complete)
FindStringWithinADatabase		Finds a string anywhere within a database. Can be useful for testing masking of data.
IsIPv4Address		Determines if an input string is a valid IP v4 address
IsJobRunning		Determines if a SQL Server Agent job is running
IsLockPagesInMemoryEnabled		Checks if LPIM is enabled
IsXActAbortON		Checks if XACT_ABORT is on
LastSQLServerRestart		Returns the last time the SQL Server was restarted
LatestSQLServerBuilds		View returning latest release and patch level for all supported SQL Server versions
ListAllDataTypesInUse		Lists all data types in use
ListColumnsAndDataTypes		Lists the data types for all columns (user tables only)
ListConstraintsWithSystemNames		List constraints that have system generated names (prefer appropriate assigned names)
ListDisabledIndexes		List indexes that are disabled with both key and included column lists
ListEmptyUserTables		Lists the schema and table names for all empty tables
ListForeignKeyColumns		List foreign keys with both source and referenced columns
ListForeignKeys		List foreign keys with column lists
ListIncomingForeignKeys		List foreign keys with column lists filtered by the target schemas and tables
ListIndexes		List indexes with both key and included column lists
ListInstalledSQLServerInstances		Lists all installed SQL Server instances
ListMismatchedDatabaseCollations		List databases with collations that don't match the server's collation
ListMismatchedDataTypes		List columns with the same name that are defined with different data types (user tables only)
ListNonIndexedForeignKeys		List foreign keys where the foreign key columns are not present as the first components of at least one non-clustered index
ListPotentialDateColumns		Lists columns that are named as dates but use datatypes with time
ListPotentialDateColumnsByValue		Lists columns that are defined with datatypes that include a time component but no time value is present
ListPrimaryKeyColumns		Lists the columns that are used in primary keys for all tables
ListSubsetIndexes		Lists indexes that appear to be subsets of other indexes
ListUntrustedCheckConstraints		Lists untrusted check constraints. Ignores disabled constraints.
ListUntrustedForeignKeys		Finds foreign keys that are not trusted
ListUnusedIndexes		List indexes that appear to be unused (user tables only)
ListUseOfDeprecatedDataTypes		Lists any use of deprecated data types (user tables only)
ListUserDefinedDataTypes		Lists the user defined data types in a database
ListUserHeapTables		Lists the user tables that do not have a clustered index declared
ListUserTableAndIndexSizes		Lists the size and number of rows for all or selected user tables and indexes
ListUserTableSizes		Lists the size and number of rows for all or selected user tables
ListUserTablesWithNoPrimaryKey		Lists the user tables that do not have a primary key declared
ListViewColumnsAndDataTypes		Lists the data types for all columns (user views only)
NonDefaultSystemConfigurationOptions		View returning SQL Server Configuration options that are not at their default values
OperatingSystemConfiguration		Configuration of the current operating system
PGObjectName		Converts a SQL Server object name to a PostgreSQL object name
PrintMessage		Prints a message immediately rather than waiting for PRINT to be returned
ProductVersionComponents		Extracts the components of a product version
ProductVersionToBuild		Extracts a product 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)
ReadCSVFile		Reads a CSV file's data and outputs it as a set of columns
ReseedSequenceBeyondTableValues		Sets the sequence to a value beyond any column value that uses it as a default
ReseedSequences		Sets the sequences to a value beyond any column value that uses them as a default
ResetSequence		Resets the next sequence value to start with 1
RetrustCheckConstraints		Tries to retrust untrusted check constraints. Ignores disabled constraints.
RetrustForeignKeys		Tries to retrust untrusted foreign keys. Ignores disabled foreign keys
ScriptAnalyticsView		Scripts a data model table as an analytics view
ServerMaximumDBCompatibilityLevel		Returns the maximum database compatibility level for the server
SetAnsiNullsOnForTable		Changes the ANSI Nulls setting for a table to ON
SetAnsiNullsQuotedIdentifierForStoredProcedures		Changes the ANSI_NULLS and QUOTED_IDENTIFIER settings for selected procedures
SetDatabaseCompabilityForAllDatabasesToMaximum		Sets the database compability level for all databases to the maximum supported level
ShowBackupCompletionEstimates		Shows completion estimates for any currently executing backups
ShowCurrentBlocking		Lists sessions holding locks, the SQL they are executing, then lists blocked items and the SQL
Sleep		Sleeps for the given number of seconds
SQLServerType		Returns the type of SQL Server for the current session
SQLServerVersion		Returns the version of SQL Server (e.g. 2012, 2014, 2008R2)
SQLServerVersionForCompatibilityLevel		Converts a database compatibility level to a SQL Server version
TableOfNumbers		Returns a table of numbers with a specified number of rows from the specified starting value
UpdateStatistics		Update statistics for selected set of user tables

Reporting Services Functions

Topic		Description
RSListContentItems		Details of all reporting services content (reports, folders, etc.)
RSListUserAccess		Details Reporting Services users and their roles
RSListUserAccessToContent		Details which RS users have access to which content (reports, folders, etc.)

Scripting Functions

Topic		Description
ExecuteOrPrint		Execute or Print One or More SQL Commands in a String
FormatDataTypeName		Converts data type components into an output string
ScriptDatabaseObjectPermissions		Scripts all database object premissions
ScriptDatabaseUsers		Scripts all database users
ScriptServerPermissions		Scripts all Server Permissions
ScriptServerRoleMembers		Scripts all Server Role Members
ScriptSQLLogins		Scripts all specified SQL logins, with password hashes, security IDs, default databases and languages
ScriptTable		Create a script for a table
ScriptTableAsUnpivot		Create a script for a table that unpivots its results. The script can be a view or a query.
ScriptUserDefinedDatabaseRolePermissions		Scripts all permissions for user-defined database roles
ScriptUserDefinedDatabaseRoles		Scripts all user-defined database roles, including disabled state where applicable
ScriptUserDefinedServerRolePermissions		Scripts all permissions for user-defined server roles
ScriptUserDefinedServerRoles		Scripts all user-defined server roles, including disabled state where applicable
ScriptWindowsLogins		Scripts all specified Windows logins, with default databases and languages

Performance Tuning Functions and Procedures

Topic		Description
AnalyzePerformanceTuningTrace		Analyzes a loaded performance tuning trace file in terms of both normalized and unnormalized forms
CapturePerformanceTuningTrace		Captures a performance tuning trace file then terminates
DeExecuteSQLString		Locates the command and reorganizes parameters from within an sp_executeSQL string
ExtractSQLTemplate		Extracts a query template from a SQL command string
LastParameterStartPosition		Locates the starting position of the last parameter in an sp_executeSQL string
LoadPerformanceTuningTrace		Loads a performance tuning trace file and optionally normalizes the queries in it

Utility and Reference Views

Topic		Description
Countries		View listing all countries
Currencies		View returning common world currencies
CurrenciesByCountry		View listing countries and the currencies they use
FutureReservedWords		View returning SQL Server declared future reserved words, and the color they normally appear in SSMS
Languages		View returning all the world languages
LoginTypes		Maps login types to names and descriptions
ODBCReservedWords		View returning SQL Server ODBC reserved words, and the color they normally appear in SSMS
OperatingSystemLocales		View returning locales used by operating systems
OperatingSystemSKUs		View returning SKUs used by operating systems
OperatingSystemVersions		View returning names of operating systems by versions
ReservedWords		View returning SQL Server reserved words, and the color they normally appear in SSMS
RSCatalogTypes		Maps reporting services catalog types to names
SQLServerProductVersions		View returning Product Versions for SQL Server
SystemConfigurationOptionDefaults		View returning SQL Server Configuration Option Default values
SystemDataTypeNames		View returning SQL Server system data type names, and the color they normally appear in SSMS
SystemWords		View returning SQL Server reserved words, future reserved words, ODBC reserved words, and system data type names
UserTypes		Maps user types to names and descriptions