SDU Tools: List Foreign Key Columns in a SQL Server Database

In a previous post, I talked about the ListForeignKeys procedure as part of our free SDU Tools for developers and DBAs. That procedure returned one row per foreign key. Sometimes though, you need to process each column of a foreign key separately. So we've provided the ListForeignKeyColumns tool to do that.

The tool also detects any keys that are using system-generated names. (We don't recommend that).

You can see how to execute it in the main image above. The procedure takes these parameters:

@DatabaseName sysname – This is the database to process
@SchemasToList nvarchar(max) – a comma-delimited list of schemas to include (ie: 'Sales,Purchasing') or the word 'ALL'
@TablesToList nvarchar(max)- a comma-delimited list of tables to include (ie: 'Customers,Orders') or the word 'ALL'

One row is returned for each foreign key column, rather than for each foreign key.

The columns returned are ForeignKeyName, IsDisabled, IsNotTrusted, IsSystemNamed, SchemaName, TableName, ColumnID, ColumnName, ReferencedSchemaName, ReferencedTableName, ReferencedColumnName.

You can see it in action here:

To become an SDU Insider and to get our free tools and eBooks, please just visit here:

http://sdutools.sqldownunder.com

SQL: SET NOCOUNT ON in SQL Server Triggers

I've done a lot of Microsoft certification exams over the years. Most of those have been SQL Server exams. The quality of the questions in those exams though, varies enormously.

One of the exams that really drove me crazy a while back was one of the intro-level ones. The problem was with the number of errors in the exam. When the question-writer gives you five substantial blocks of T-SQL, and asks which one is correct, yet they are all actually wrong in some way, that makes it a very, very tough question to answer. I find these types of exams very tiring because I'm endlessly trying to second guess what the question writer actually meant to ask. Some exams have a large number of these.

A more subtle problem though, is when there are questions that aren't really wrong, but it's immediately obvious that the question-writer just didn't "get" something. Here's an example:

One of the very first things that we teach people about writing DML triggers (ie: INSERT, DELETE, UPDATE triggers) is about the use of SET NOCOUNT ON. Yet on T-SQL exams that involve these triggers, I've lost count of how many triggers I've seen that don't include this.

What that tells me is that the question-writer has read about triggers and has a theoretical understanding of them, but hasn't really spent time implementing them.

If the question-writer was used to writing triggers, they'd put this in almost every time, without even thinking. Let me explain why.

We'll start with a simple table:

I'll then update one row:

Note that it shows 1 row affected as expected. If I execute that UPDATE statement from an application, and I expect it to update one row, the code will often check that one row was affected.

But now let's add an auditing table and a trigger:

After doing this, note what happens with our previous UPDATE:

This will not be what the application is expecting. The two sets of affected row counts might well be a problem. Worse, imagine if the second one had two rows affected in the background. The app might total the rows affected, or it might use the last value. Either way it'll be wrong.

Now let's alter the trigger to add SET NOCOUNT ON:

When we then do the update, we're back to what we expected:

So, while there are exceptions to any rule, in general when writing DML triggers, you should have SET NOCOUNT ON in the code.

Shortcut: Finding error locations within queries in SQL Server Management Studio

This is probably one of the simplest tips that I've published, yet I'm endlessly surprised how many people do not realize that it's available.

When you have a script loaded in SQL Server Management Studio (SSMS), and you execute the script, you might run into an error like this:

To find where the error is, just double-click the error down in the Messages tab. I double-clicked it, and it took me directly to the error and highlighted it:

SDU Tools: List Foreign Keys in a SQL Server Database

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:

@DatabaseName sysname – This is the database to process
@SchemasToList nvarchar(max) – a comma-delimited list of schemas to include (ie: 'Sales,Purchasing') or the word 'ALL'
@TablesToList nvarchar(max)- a comma-delimited list of tables to include (ie: 'Customers,Orders') or the word 'ALL'

One row is returned for each foreign key.

The columns returned are SourceSchemaName, SourceTableName, ForeignKeyName, SourceColumnList, ReferencedSchemaName, ReferencedTableName, ReferencedColumnList, IsNotTrusted, IsDisabled.

You can see it in action here:

To become an SDU Insider and to get our free tools and eBooks, please just visit here:

http://sdutools.sqldownunder.com

SQL: Version 12 of SDU Tools has shipped

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.

This release includes the following new tools (click on any one of them to see a video about it):

ScriptTableAsUnpivot : T-SQL has an UNPIVOT operator that can convert columns in table to rows in the output. The syntax is, however, a little hard to learn. (The same applies to PIVOT). Many people though, want to access standard relational tables as lists of attributes. This tool helps to write code for that, either as queries based on the table's primary key, or as a view that can be later queried. This can also help if you want to automate the creation of unpivoted views for your tables. If you need to access a standard table like it's an entity-attribute-value table (list of attributes and values), this tool can help.

NumberToRomanNumerals : We often get odd requests for new tools. For v12, it's this one. It takes a number (including large numbers), and returns a string representing the number in Roman Numerals.

ListDisabledIndexes : Does what the name says. It lists any indexes that are currently disabled. While these might be intentional, we often find they are accidentally left behind after data load processes, and cause performance problems.

ListUserHeapTables : In SQL Server, a heap is a table that doesn't have a clustered index. While there are tables that are best structured as heaps, most tables work best with a clustered index, particularly if there are other nonclustered indexes associated with them. What we often find though, is that this happens by accident, not by design. This tool helps to find any tables that are heaps.

ListUserTablesWithNoPrimaryKey : In SQL Server, a primary key is used to uniquely identify a row in a table. It is one or more columns that are both unique and not null. Generally, all tables should have primary keys. This tool helps to find any tables that don't have primary keys.

SetAnsiNullsQuotedIdentifierForStoredProcedures : This T-SQL stored procedure cleans up ANSI_NULLS and QUOTED_IDENTIFIER settings for one or more stored procedures. It can be used to fix procedures that have been inconsistently defined.

IsWeekday and IsWeekend : These T-SQL functions can be used to determine if a given date is a week day (Monday to Friday) or a weekend (Saturday or Sunday).

We hope you find these tools useful!

Shortcut: Change the number of rows selected or edited in Object Explorer in SSMS

When you right-click a table in SQL Server Management Studio, you get options for selecting or editing but the number of rows is limited:

Those values can be changed. By default, these numbers are both 200, but I've decided to change the default number of rows selected to 1000.

In Tools, Options, SQL Server Object Explorer, then Commands, you can set the values to whatever suits you:

I don't tend to ever use the Edit option but I'd suggest not making it too large.

SDU Tools: Is XACT_ABORT on in my SQL Server session?

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:

SET XACT_ABORT ON;
SET NOCOUNT ON;

In our free SDU Tools for developers and DBAs,  we added a function IsXactAbortOn to let you determine in code, if it's enabled.

You can see the outcome in the main image above.

You can see it in action here:

To become an SDU Insider and to get our free tools and eBooks, please just visit here:

http://sdutools.sqldownunder.com

SQL Server Indexing for Developers – new online on-demand training course

Do you need to understand SQL Server indexing in detail? Do you know someone else who needs to? Either way, our new SQL Server Indexing for Developers course could be just what you're looking for.

It's detailed. It has a full set of practical exercises (hands-on labs) with downloadable test data and scripts.

The best part is it's on sale until October 31st for $195 USD as an introductory special.

What Greg will teach you:

  • How SQL Server indexes really work
  • How to design effective SQL Server indexes
  • Why SQL Server seems to be ignoring the indexes that you designed
  • How data type choices affect indexes
  • If filtered indexes would work better in your application
  • How to check whether or not SQL Server "likes" your index
  • How to avoid having too many indexes (and how many is too many)
  • Which indexes you can safely remove

You'll find more info here:

https://training.sqldownunder.com/p/sql-server-indexing-for-developers

 

Shortcut: XML editor in SSMS and increasing XML output size

Most people use SQL Server Management Studio (SSMS) to edit SQL queries. No big surprise there. The files will have a file type of .sql.

But what many people don't understand is that SSMS inherits many of its underlying Visual Studio's abilities to edit other document types.

For example, if you open a .txt text file, you can edit it just fine, and you can also include files like this in SSMS script projects. That can be useful for additional notes and documentation.

SSMS also knows how to open related file types like .sqlplan (for query plans) and .xdl files (for deadlocks), and more.

Most of these other file types though, are actually XML files with specific schemas constraining their contents. SSMS also contains a perfectly acceptable XML editor.

Here's an example:

If I execute the above query, the outcome is some XML. Note that SSMS recognizes that the output data type is XML and then provides a hyperlink for opening it. If I click on the link, I see this:

The important item to notice here though is the red squiggly on the second line. If we hover over that, we'll see this:

XML documents can only have a single root element. This XML is actually a fragment, not a complete document, and so it thinks that all the stock item lines are all root elements.

The important thing is that this is an XML editor, not just an XML viewer. Notice that when an XML file is open, an XML menu also appears:

Now, while it's not a bad XML editor, it has limits on the size of the data that you can work with, but you can control that too. In Tools, Options, Query Results, SQL Server, Results to Grid, you can see this:

By default, you are limited to 2MB of XML data. You can increase this to unlimited but keep in mind that SSMS is (unfortunately) still a 32 bit application and can struggle to work with gigantic files.

SDU Tools: Is a SQL Server Agent Job Running ?

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:

To become an SDU Insider and to get our free tools and eBooks, please just visit here:

http://sdutools.sqldownunder.com