Filtering Tables, Procedures, And Other Objects In SSMS

Published on: 2018-06-26

While the text of this post contains good information on SSMS object filters, I highly recommend watching this week’s video on YouTube – I stretched my creativity with filming this week while I was on vacation.

This week I’m sharing a trick I learned at Jess Pomfret‘s Ohio North SQL Server User Group presentation on data compression.  Her presentation on data compression was awesome (check it out if you get the chance), but I was shocked that I have been working with SSMS for so many years and have never known about the following trick I watched her perform in her demos.

An “Organized” Nightmare

I’m guessing you’ve probably worked in a database that has hundreds or thousands of database objects:

I often come across this in applications where for one reason or another someone decides that there is no need to separate apps into different databases; why bother creating different databases when you can have lots of different schemas to organize your objects instead!? (*cough* vendor applications *cough*).

The problem with these enormous lists of tables, procedures, functions, etc… is that it can get pretty tiring to scroll through them to find what you need.

For years I wore down my mouse’s scroll wheel, scrolling between thousands of objects across multiple server instances.  As it turns out, SSMS has a much better feature for handling this problem.

Filtering Objects in SSMS

You can apply filters to most objects in SSMS by right clicking and choosing “Filter Settings”:

You can filter on attributes such as name, schema, create date, etc…:

For example, if I want to see only tables that are in the Travel schema, all I have to do is create a filter:

SSMS will even tell me that my list of objects is filtered so I don’t go crazy later on wondering where all of my other tables went.

Limitations

There are a few limitations with using SSMS object filters though.

For example, the different filter attributes work together as if they had “AND” operators between them, so you can do something like filter on tables in the Travel schema that contain the letter “a”:

However, there is no way to write multiple conditions with OR logic (eg. you can’t filter on the schemas “Travel” OR “Lodging”).

And while SSMS will indicate that your objects are filtered, it won’t persist that filter after restarting SSMS.

Even with those drawbacks, I’ve used this filtering feature at least once per week since learning about it; it saves a lot of time and I can’t believe I went so long without knowing about it.

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!

What Does The GO Command Do?

Published on: 2018-05-29

Watch this week’s episode on YouTube.

When I started working with T-SQL, I thought the GO command was optional, kind of like semicolons.  It appeared in plenty of SSMS generated scripts, but it seemed like I never had to add it to any queries of my own:

Turns out that GO isn’t T-SQL at all, but a command that allows apps (like SSMS) to send batches of queries to SQL Server.  It also turns out it has some more functionality than I originally thought.

So when should you use GO?

Using GO When Required

Just like semicolons not being entirely optional (ever try to run a CTE after a statement without a semicolon?), GO isn’t completely optional either.

Some operations require that GO appear immediately after them:

The above script will fail with the error message “‘CREATE/ALTER PROCEDURE’ must be the first statement in a query batch.”

There are multiple commands (“CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE SCHEMA, CREATE TRIGGER, and CREATE VIEW”) that require being the first statement of a batch, so using GO is required if you are going to try running other statements as part of your script.

So how do you run that CREATE PROCEDURE statement after first checking and dropping that procedure?  Just add GO so that CREATE PROCEDURE is the first statement of the batch:

Executing Commands Multiple Times

So the previous example was one where SQL Server required me to type two extra characters to run certain commands.  Boo.  What about something actually useful I can do with GO?

Sometimes you may want to run a statement more than once.  You can do that by being trigger happy with the F5 key or your mouse button, but you don’t want to do that 10,000 times, do you?

That’s where GO shines.  Simply add an integer after GO and SQL Server will execute that batch of statements however many times you specified.  For example, the following code will insert 10,000 rows into a table:

This is really useful when creating test data or simulating workloads.

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!

The Fastest Way To Locate Errors In Your SQL Query

Published on: 2018-01-30

Photo by N. on Unsplash

In about 60 seconds you will never debug error messages in SQL Server Management Studio the same way again.

Coming from a .NET background, I’m used to receiving relatively helpful error messages that point me to the precise location of the error in my code:

Pinpoint error finding

SQL Server Management Studio does a decent job too – except when it doesn’t.  Different versions of SSMS provide error line numbers differently.

Watch this week’s episode on YouTube!

In older versions (<= 2012), SSMS provides error location information relative to the batch starting point of the erroneous query.  This means if you have a window full of T-SQL batch statements, your error message will be relative to the last “GO” in the editor before the syntax error:

In SSMS 2014, batch start locations aren’t considered:

Starting in SSMS 2016, the error messages revert back to counting from the current batch but also indicate the line number where the batch begins:

While all of these error messages point me in the right direction, I’m too lazy to try and remember how each version of SSMS refers to the error location.  This becomes particularly problematic when jumping around environments and not being able to use a consistent version of SSMS.

There is one feature that works consistently though that makes navigating to the specific error lines easily.

You can double click on the error message to be taken directly to the line number referenced!

I know this sounds simple, but it’s one of those small things I never thought of trying until I saw someone else do it.  Little did I know what I was missing out on – especially in those giant hundreds/thousands of line long scripts.  but now that I know about it it’s the only way I locate my query errors.

This functionality also works if you have multiple errors listed in the Messages window.

But what about if your SQL query is all on one giant line (like if it was copied from somewhere or generated dynamically)?  You can use a regular expression to format your query first and then click on the error message to quickly navigate to the correct lines.

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!