I love SQL Prompt, and I’m regularly impressed by the enhancements our teams continue to make in the product. One item that I found interesting recently was schema filtering. This post takes a quick look at this feature.
If I go to look for an object, intellisense is amazing. In this case, I’ll type “SSF <tab> cust”. I’ll see this:
There is the customer table first, and as we see on the right, this is the dbo.customer table. Below this, we see there is a Customer table in the ETL schema and two CustomerLoad tables, which are in two different schemas.
I might actually be working in one of those schemas. Let’s say I’m an ETL developer. I don’t want to accidentally be coding against dbo.Customer when I want to use ETL.Customer. Certainly I might need each one, but in this case, I might just care about loading stuff into the ETL schema and want to work there.
I can add filtering in SQL Prompt by going to the Options and then the Connections section. As you can see below, I have a drop down for schemas.
The default is all schemas, in all databases, but I can change this. I can set a filter. In this case, I’ll choose the “Only load…” option. When I do this, I see a generic wildcard here.
I see schemas for all servers and databases. However, I can set filtering for a specific server and database. Or a database on all servers, or certain servers, but all databases. Here I’ll just edit the schema.
This will change behavior to only show the ETL schema in all databases on all servers. Now when I type the same thing, I see the ETL schema objects only.
Good, right? Plus, the refreshes and loads are faster. The downside here is that in another database, I see no tables.
I can fix this, and just look in that one database by entering the database name. I’d suggest you set this by server and database, just so you are clear what behavior is happening, but it’s up to you.
This was a request from developers that do work in some large schemas, and only want to see those objects. It’s been added to SQL Prompt, so update today, upgrade, or download a trial and give it a try.