Building Dynamic Table-Driven Queries

Published on: 2018-07-10

This post is a response to this month’s T-SQL Tuesday #104 prompt by me! T-SQL Tuesday is a way for SQL Server bloggers to share ideas about different database and professional topics every month.

This month’s topic is asking what code would you hate to live without?


When given the choice between working on new projects versus maintaining old ones, I’m always more excited to work on something new.

That means that when I build something that is going to used for years to come, I try to build it so that it will require as little maintenance as possible in the future.

One technique I use for minimizing maintenance is making my queries dynamic.  Dynamic queries, while not right for every situation, do one thing really well: they allow you to modify functionality without needing a complete rewrite when your data changes.  The way I look it, it’s much easier to add rules and logic to rows in table than having to modify a table’s columns or structure.

To show you what I mean,let’s say I want to write a query selecting data from model.sys.database_permissions:

Writing the query as above is pretty simple, but it isn’t flexible in case the table structure changes in the future or if we want to programmatically write some conditions.

Instead of hardcoding the query as above, here is a general pattern I use for writing dynamic table-driven queries.  SQL Server has the handy views sys.all_views and sys.all_columns that show information about what columns are stored in each table/view:

Using these two views, I can use this dynamic SQL pattern to build the same exact query as above:

The way building a dynamic statement like this works is that I build my SELECT statement as a string based on the values stored in my all_columns view.  If a column is ever added to this view, my dynamic code will handle it (I wouldn’t expect this view to change that much in future versions of SQL, but in other real-world tables I can regularly expect changing data).

Yes, writing certain queries dynamically like this means more up front work.  It also means some queries won’t run to their full potential (not necessarily reusing plans, not tuning every individual query, needing to be thoughtful about SQL injection attacks, etc…).  There are A LOT of downsides to building queries dynamically like this.

But dynamically built queries make my systems flexible and drastically reduce the amount of work I have to do down the road.  In the next few weeks I hope to go into this type of dynamically built, table-driven process in more detail (so you should see the pattern in the example above get reused soon!).

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!

Code You Would Hate To Live Without (T-SQL Tuesday #104 Invitation)

Published on: 2018-07-03

The recent news about Microsoft acquiring GitHub has me thinking about how amazing it is for us to be part of today’s online code community.

Before modern online programming communities, finding good code samples or sharing your own code was challenging.  Forums and email lists (if searchable) were good, but beyond that you had to rely on books, coworkers, and maybe a local meetup of like-minded individuals to help you work through your programming problems.

Check out this month’s T-SQL Tuesday invitation in visual form!

Today, accessing and using code from the internet is second nature – I almost always first look online to see if a good solution already exists.  At the very least, searching blogs, GitHub, and StackOverflow for existing code is a great way to generate ideas.

For this month’s T-SQL Tuesday, I want you to write about code you’ve written that you would hate to live without.

Maybe you built a maintenance script to free up disk space, wrote a query to gather system stats for monitoring, or coded some PowerShell to clean up string data.  Your work doesn’t need to be completely original either – maybe you’ve improved the code in some open source project to better solve the problem for your particular situation.

There’s probably someone out there in the world who is experiencing the same problem that you have already solved; let’s make their life a little easier by sharing.

And don’t worry if your code isn’t perfect – just explain how your solution works and if you are aware of any caveats.  If it’s not an exact solution for someone else’s problem, at the very least it may help them generate some ideas.

Finally, here’s a reminder of the official rules for T-SQL Tuesday:

  1. Publish your contribution on Tuesday, July 10, 2018. Let’s use the “it’s Tuesday somewhere” rule.
  2. Include the T-SQL Tuesday Logo and have it link to this post.
  3. Please comment below with a link to your post (trackbacks/pingbacks should work too but…comments ensure I don’t miss your post)
  4. Tweet about your post using #tsql2sday.
  5. If you’d like to host in the future, contact Adam Machanic.

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!

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!