In SQL Server Management Studio, there’s no great way to list all the foreign keys that are defined in a database.
In our free SDU Tools for developers and DBAs, we added a function ListForeignKeys to do just that, and to provide their details in a form that’s easy to consume programmatically if you need that.
You can see how to execute it in the main image above. The procedure takes these parameters:
2018-10-17
SDU Tools is a toolkit that’s simple to use, has many useful functions, procedures, and views, and is updated regularly. Even if you aren’t using the tools as a set, they provide wonderful examples of how to do things in T-SQL. We have released version 12.0 of these popular tools.
The tools are free tools for DBAS and Developers. Tools for comparing databases, tables, finding unused indexes, manipulating strings, performance tuning converting data, and so much more.
2018-10-15
XACT_ABORT is one of the least well understood options that you can configure in a SQL Server session. Yet it’s very important. XACT_ABORT makes statement-terminating errors become batch-terminating errors. Without it, even within a transaction, many errors only terminate the statement that they occur in, and control passes to the next statement within the transaction, not out of the transaction.
In nearly every stored procedure that I write, the template includes the following lines:
2018-10-10
I often need to write scripts that interact with SQL Server Agent jobs. One thing that I regularly need to know is if a particular job is currently running.
In our free SDU Tools for developers and DBAs, we added a function IsJobRunning to work that out.
You can see the outcome in the main image above.
You can see it in action here:
2018-10-03
I mentioned in a previous post about how I sometimes need to work with binary strings in SQL Server using T-SQL. The literal values are hexadecimal strings. T-SQL doesn’t have a simple function to just convert one of these character-pair strings to an integer.
In our free SDU Tools for developers and DBAs, as well as the HexCharStringToChar function, we added a function HexCharStringToInt to do just this.
You can see the outcome in the main image above.
2018-09-26
On occasions I have to work with binary strings in SQL Server using T-SQL. The literal values are hexadecimal strings. T-SQL doesn’t have a simple function to just convert one of these character-pair strings to a character.
In our free SDU Tools for developers and DBAs, we added a function HexCharStringToChar to do just this.
You can see the outcome in the main image above.
You can see it in action here:
2018-09-19
I often need to do a quick check to see if the schema of two SQL Server databases is the same.
In our free SDU Tools for developers and DBAs, there is a stored procedure called GetDBSchemaCoreComparison to make that easy. It takes the following parameters and returns a rowset that’s easy to consume programmatically (or by just looking at it):
@Database1 sysname -> name of the first database to check @Database2 sysname -> name of the second database to compare @IgnoreColumnID bit -> set to 1 if tables with the same columns but in different order are considered equivalent, otherwise set to 0 @IgnoreFillFactor bit -> set to 1 if index fillfactors are to be ignored, otherwise set to 0
2018-09-05
We’ve been building tools to create scripts for various SQL Server T-SQL objects for a long time. Part of scripting a table is the scripting of the data type. That means its datatype name, precision, scale, and maximum length.
In our free SDU Tools for developers and DBAs, we added a scalar function called FormatDataTypeName ot make that easy. It takes the following parameters:
@DataTypeName sysname - the name of the data type @Precision int - the decimal or numeric precision @Scale int - the scale for the value @MaximumLength - the maximum length of string values
2018-08-29
Fellow MVP Paul White and others have discussed the indexing of small segments of strings, to make fast indexes that I think are somewhat like full-text indexes but potentially more efficient. Three character substrings are common, and are known as trigrams.
I’ve experimented with these at a number of site and seen really interesting (good) results.
In our free SDU Tools for developers and DBAs, we added a table-valued function ExtractTrigrams to help with this. You can pass it a string, and it will pull it apart for you, and return the set of trigrams. This would make it much easier for you to experiment with these types of indexes.
2018-08-22
Occasionally I’ve needed to take a string, and extract all the words out of it. For example a string like ‘hello there greg’ might lead me to want the three words ‘hello’, ’there’, and ‘greg’. Note that I usually want them trimmed, not just extracted.
In our free SDU Tools for developers and DBAs, we added a table-valued function ExtractTrimmedWords to help with this. You can pass it a string, and it will pull it apart for you, assuming that you have whitespace separating the words.
2018-08-15
