Auto Complete - can object matching ignore schemas?

Hello,

I'm evaluating SQL Assistant Pro. We are accustomed to using Redgate's SQL Prompt which ignores schemas when matching object names. Is there a configuration option for the same or similar effect in SQL Assistant?

For example, I type "exec timesheet" and I hope to see stored procedures with names like this:
PM.spBuildTimesheetPTO
Report.spTimesheetSendReminders
Timesheets.spCreateMissingPTOTimesheets

But today, I only see the 3rd.

We use many schemas to organize database objects and it's not feasible for our developers to remember the schema to which a specific object belongs.

Thanks!

There is no such option out of the box. SQL Assistant's SQL Intellisense is designed to be code context driven.
You can create a code snippet though to mimic the described behavior, and assign a hot key so you can easily invoke it when wanted.

Thanks for the clarity. Unfortunately, this is a key use case for us.

Our databases are large and complex with many objects. For instance, one (out of roughly 50 active databases) has 498 tables organized into 22 schemas and 1,967 stored procedures organized into 39 schemas.

A few hundred tables isn't really big, rather a relatively small number :-)
Some databases contain tens and hundreds of thousands of tables and procedures.

As suggested earlier, you should be able to create a code snippet to support what you described.

As SysOp said, a database with "only" hundreds of tables isn't really big. Having stored procedure numbers nearing 2k is getting there, but still not unusual.

The reason you see Timesheets.spCreateMissingPTOTimesheets and not the others when trying to filter on stored procedures is most likely due to your default schema for your connection being set to Timesheets and not that SA ignores the schemas in names or not. The popups allow filtering on names, including those of the databases and the schemas in the current database. However, when filtering for other objects, like tables, views, functions, procedures, triggers, etc., the filtering happens either on items in your default schema (these you can filter directly) or in the schema you expand (with Right arrow key).

So it kinda expects you to have a vague knowledge of the system you are working on (how/where the different objects are organized), especially if things get complicated. In many cases/places that is not an issue, because database design was done sloppily (if at all) and everything is just dumped into the dbo schema, and everyone has that schema as their default. So everything is right there.

This, of course, is like throwing everything in your kitchen into a single drawer. It causes a mess. That mess might not be large enough to matter, but we're not talking about a small number of objects here. Having thousands of objects without any organization can clearly get in the way.

Now, while this "ignore schemas / list everything" might sound appealing, it would be just like you had a nice, neatly organized kitchen, but still decided to pull all the drawers and throw everything on the floor of your kitchen, just that you can rummage around to find what you seek. Having that "vague knowledge" I mentioned and using Name Contains Characters from Key String, Order by Best Match as Name Matching Method with the "correct" order of the objects in the popup (schemas up front, in this case), filtering on on those stored procedures in your example could be done in 4-10 keypresses, 2-3 for picking the schema, a right arrow for expanding it, and the rest for choosing the desired stored procedure.

Using the proposed "ignore schema" feature, and having just a couple dozen stored procedures matching the same filtering condition in different schemas will give you all of those objects after filtering. I might be wrong about it but I guess you have more than 3 stored procedures with the string "timesheet" in their names in that list of 1967.
At that point you'd have to:
a) either choose a different filtering condition to avoid having so many to choose from
b) or to traverse that list using the Up/Down arrows + PgUp/PgDown to find your Holy Grail.
That could easily accumulate to dozens of keypresses above the ones you used for the filter.

As for your problem, I'm not quite sure it can be solved with snippets. Even if you create a snippet that uses $OBJECT(proc, ins_object)$, that macro is still subject to the same limitations, that is, it will only list (and filter on) the objects in your default schema, and will allow filtering on objects in other schemas when you explicitly tell it to do so, meaning that you're going to have to expand that schema first. Unless, of course, there's a switch or option somewhere that would produce this behavior. I must check this.

Regardless, this feature could still help when you don't have that "vague knowledge". But it will definitely hinder you in every other case. If this "ignore schema" feature is considered at all, in my opinion, it would be a fine candidate as an option for the $OBJECT(...)$ macro. I'd also expect making this work to be a heavy punch to the gut of how handling schemas and the object is done behind the curtains in the popup, so I'm not sure the developers are eager to throw everything out the window to re-design it from scratch.

With that said, I even support the idea. It would be quite an aid when getting acquainted with a new database design/organization/whatever for the first time (or several first times). I usually know where I put my things but have experience with other developers hiding their stuff in unanticipated places. I've created a snippet that lists all the stored procedures (with filter of course), so I can track those when needed, and it helps learning the architecture, but that is a two-step process. This extra option might make it a one-step. I might actually use it. I'd create an "exec-i-am-wise" and an "exec-i-am-dumb" snippet with that option :D