Schema Filtering in SQL Prompt

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:

2020-02-27 15_03_37-ObjectDefinitionBox

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.

2020-02-27 15_05_03-SQL Prompt – Options

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.

2020-02-27 15_06_45-SQL Prompt – Options

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.

2020-02-27 15_06_05-SQL Prompt – Options

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.

2020-02-27 15_08_32-ObjectDefinitionBox

Good, right? Plus, the refreshes and loads are faster. The downside here is that in another database, I see no tables.

2020-02-27 15_09_14-CandidateList

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.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.