SDU Tools


Free Tools for DBAs and Developers

 

Tools for comparing databases, tables, finding unused indexes, manipulating strings, performance tuning, converting data, and so much more.

And did we mention, free?

(Latest release v12)

What are the SDU Tools? 

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 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)

 

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 FUNCTIONS
ProperCase Converts a string to Proper Case
TitleCase Converts a string to Title Case
CamelCase Converts a string to camelCase
PascalCase Converts a string to PascalCase
SnakeCase Converts a string to snake_case
KebabCase Converts a string to kebab-case
TrainCase Converts a string to Train-Case
ScreamingSnakeCase Converts a string to SCREAMING_SNAKE_CASE
SpongeBobSnakeCase Converts a string to sPoNgEbOb_sNaKeCaSe
PercentEncode Encodes reserved characters that are used in HTML or URL encoding
AsciiOnly Removes non-ASCII characters from a string
DigitsOnly Removes non-digit characters from a string
QuoteString Quotes a string
LeftPad Left pads a string
RightPad Right pads a string
SeparateByCase Inserts spaces in Pascal-cased or camel-cased strings
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
TrimWhitespace Removes any leading or trailing space, tab, carriage return, and linefeed characters
StartOfFinancialYear Calculates the date for the start of the financial year
StartOfMonth Calculates the date for the start of a month
EndOfMonth Calculates the date for the end of a month
EndOfFinancialYear Calculates the date for the end of the financial 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
CountWords Counts the number of words in a string
ExtractTrimmedWords Extracts words from a string and trims them
ExtractTrigrams Extracts trigrams (segements of up to 3 characters) from a string for fast searching
InvertString Returns an upside down version of the string
Sleep Sleeps for a number of seconds
Translate Replaces a set of characters in a string
DateDiffNoWeekend Calculates the number of days between two dates excluding weekends (Saturday and Sunday)
XMLEncodeString Performs XML encoding on a string
XMLDecodeString Decodes an XML encoded string
PrintMessage Prints a message immediately (unlike PRINT)
DropTemporaryTableIfExists Drops a temporary table if it exists
PreviousNonWhitespaceCharacter Finds the previous non-whitespace character working backwards from the current position
AlphanumericOnly Removes any non-alphanumeric characters from a string
AlphabeticOnly Removes any non-alphabetic characters from a string
DateOfEasterSunday Works out when Easter Sunday (Christian Easter) occurs in a particular year

 

DATABASE UTILITIES
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
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
FindSubsetIndexes Finds indexes that appear to be subsets of other indexes in all databases or selected databases
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
ListDisabledIndexes Lists disabled indexes
ListUserHeapTables Lists user tables that are heaps (no clustered index)
ListUserTablesWithNoPrimaryKey Lists user tables with no primary keys
ListForeignKeys Lists foreign keys and their columns as column sets
ListForeignKeyColumns Lists foreign keys and their columns as rows
ListNonIndexedForeignKeys Lists foreign keys not supported by indexes
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
ListUnusedIndexes Lists unused indexes for a database
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
ListUserTableSizes Lists row counts and sizes for user tables
ListUserTableAndIndexSizes Lists row counts and sizes for user tables and indexes
ReadCSVFile Reads columns from a CSV file
EmptySchema Removes all user objects from the selected schema
IsXActAbortON Determines if XACT_ABORT is currently on
ShowBackupCompletionEstimates Show the status of currently-executing backups and estimate the completion time
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
ExecuteJobAndWaitForCompletion Executes a SQL Server Agent job synchronously (waits for it to complete)
ClearServiceBrokerTransmissionQueue Clears service broker transmission queue entries and conversations
UpdateStatistics Updates statistics on a set of tables and or schemas or a complete database
IsJobRunning Determines if a SQL Server Agent job is currently running
SetAnsiNullsOnForTable Changes a table defined with ANSI NULLS off to a table with it on
SetAnsiNullsQuotedIdentifierForStoredProcedures Fixes ANSI NULLS and QUOTED IDENTIFIER for stored procedures
ReseedSequenceBeyondTableValues 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
ReservedWords Lists T-SQL reserved words
FutureReservedWords Lists T-SQL future reserved words
ODBCReservedWords Lists ODBC reserved words
SystemDataTypeNames Lists system data type names
SystemWords Lists system reserved words
CreateLinkedServerToAzureSQLDatabase Creates a linked server to Azure SQL Database
SystemConfigurationOptionDefaults Lists default values for system configuration options
SQLServerVersionForCompatibilityLevel Determines a SQL Server version number for a database or server compatibility level

 

SCRIPTING FUNCTIONS  
ScriptSQLLogins Scripts (all or selected) SQL Logins along with security ID default database, password hash, default language, and policy
ScriptWindowsLogins Scripts (all or selected) Windows Logins along with default database and default language
ScriptServerRoleMembers Scripts all server role membership for all or selected logins
ScriptServerPermissions Scripts all server permissions (apart from CONNECT SQL) for all or selected logins
ScriptDatabaseUsers Scripts all users associated with a login for a particular database
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
ScriptTable Scripts a SQL Server table with many configuration options
ScriptTableAsUnpivot Scripts a SQL Server table as an UNPIVOT statement or view

 

DATA CONVERSION FUNCTIONS  
NumberAsText Converts a number to English text
Base64ToVarbinary Converts a base 64 value to varbinary
VarbinaryToBase64 Converts a varbinary value to base 64 encoding
CharToHexadecimal Converts a character to a hexadecimal string
SQLVariantInfo Returns information about a sql_variant value
SecondsToDuration Converts a number of seconds to a duration string
HexCharStringToInt Converts a two char hex string to an int
HexCharStringToChar Converts a two char hex string to an ASCII character
JulianDayNumberToDate Converts a Julian Day to a date
DateToJulianDayNumber Converts a date to a Julian Day
NumberToRomanNumerals Converts a number to Roman Numerals
ProductVersionToMajorVersion Converts a product version to a major version
ProductVersionToMinorVersion Converts a product version to a minor version
ProductVersionToBuild Converts a product version to a build number
ProductVersionToRelease Converts a product version to a release number

 

GENERAL PURPOSE FUNCTIONS  
TableOfNumbers Table valued function that provides a sequence of integers of a defined length, starting at a specific value
DatesBetween Table valued function to return all dates between two dates
DateDimensionColumns Table valued function to return date dimension columns for a date
TimePeriodDimensionColumns Table valued function to return time period dimension columns for a time
GetDateDimension Gets all date dimension columns for a range of dates
GetTimePeriodDimension Gets all time period dimension columns for a day
SQLServerProductVersions Lists all versions of SQL Server
ProductVersionComponents Lists all components of a product version
OperatingSystemVersions Lists all operating system versions
OperatingSystemLocales Lists all operating system locales
OperatingSystemSKUs Lists all operating system SKUs
OperatingSystemConfiguration Lists the configuration of the current operating system

 

PERFORMANCE TUNING RELATED FUNCTIONS AND PROCEDURES  
CapturePerformanceTuningTrace Captures a trace needed when carrying out performance tuning.
LoadPerformanceTuningTrace Loads a trace file into a table
AnalyzePerformanceTuningTrace Analyzes the queries that are found in a loaded trace file.
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)
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
LastParameterStartPosition Starting at the end of the string, finds the last location where a parameter is defined, based on a @ prefix

 

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 repurpose them, redistribute, or resell them.