See through schema when crossing databases

buckley

There is a small inconsistency when resolving object names but it makes a big difference.

Prompt "sees" trough the schama's when typing something after Exec

For example when typing "Schedul" it suggests

EXEC Applications.usp_ScheduledCreationOfSecurityObjects

Yay for
1. not making you type schema name as its a detail that you don't think of when in the flow of typing
2. partial match, I don't have to type usp_ in front of it.

The inconsistency is that this does not working when crossing databases

For example when typing "ActiveDirectory.Schedul" (the database where the sproc is in is called ActiveDirectory) it suggests no relevant matches.
Only when the schema is specified the completion kicks in.

Prompt would read me mind a little more if it also saw through the schemas here

Aaron L

Hi Buckley,

Thanks for the suggestion! At the moment we just suggest the immediate children of the object used for qualifying. This works well for schemas as if you've qualified with a schema you only want objects belonging to it.

But for databases I think you're right, it would probably be nicer here if it offered all schemas on the database and their children too.

We've got a 6.5 beta coming out next week so I'll see if I can experiment with expanding the database suggestions a bit more in that

Thanks!
Aaron.

buckley

Hi Aaron,

That would be great and I'll be more than happy to test it for you

"we just suggest the immediate children of the object used for qualifying."

When you are in a database and don't qualify with a schema Prompt will suggest all the objects no matter what schema they belong to. That is the behavior we see and is very helpful. So in this case the current Prompt thinks further than the immediate children.
Of course, once you qualify with a schema, the objects are suggested are scoped to that schema (container).

A database name is a also a container but most of the time you have a sql object in your head and since its not in your current database you forget to type the extra container, the schema.

Happy coding, Tom

buckley

It's actually simple now that I had a night sleep.

Only when a schema is specified the developer wants to scope the suggestions to it. Otherwise the developer probably has just the object name in mind.

Thats how I at least think, in term of object names (and database names).

I think the majority of tsql developers don't use schema's. We just recently started using them and then the suggestions are sometimes too limited because Prompt is expecting a schema name.

Aaron L

Hi Tom,

We've just release the 6.5 beta (forum post here) which should include suggesting all objects after qualifying with a database name. Please give it a go and let us know if it works as you'd expect.

Thanks!
Aaron.

buckley

Hi Aaron,

Thanks for the update. I did the tests again.

Either I don't understand how it should work or something is wrong with my installation?

The sql objects (udf,sproc and table) reside in another database that I'm in but don't show up in the intellisense.

Also, I saw the screenshot below in the Prompt changelog. It looks different than mine right? esp. the schema that is prefixed in the changelong version.

Aaron L

Hi Tom,

I'm getting 403 errors when I try to view your images off dropbox (maybe they're not shared publicly?)

For the showing the schema names it's the small arrow at the bottom left of the suggestions window that triggers their display.

I'm wondering if something is strange about your settings considering your other post too, maybe some legacy settings have been moved over if you've used an older version of Prompt. You could try resetting them to their defaults by closing SSMS and then renaming or moving %localappdata%Red GateSQL Prompt 6 temporarily (as well as any older SQL Prompt directories to avoid an auto-upgrade). When you start SSMS again you'll get all the defaults, maybe see if that helps?

Thanks,
Aaron.

buckley

Hi Aaron,

You are right about the dropbox links. Here are public versions

https://www.dropbox.com/s/9f8pj5to7z2p9u6/0002.jpg?dl=0
https://www.dropbox.com/s/bz6wgxwm79qiw3t/0003.jpg?dl=0
https://www.dropbox.com/s/8yn7j8m5knvhw0d/0004.jpg?dl=0
https://www.dropbox.com/s/fv85zt6jgi00dog/0005.jpg?dl=0
https://www.dropbox.com/s/j4ul3vrr8yrxn4a/0006.jpg?dl=0

I'll following your instructions

buckley

Ha! Wonderful now that I can see the shema by clicking that little arrow. I hadn't figured it out.

Maybe its more discoverable thing to enable it by default once a database has objects belonging to a schema other than dbo. The user can still collapse it afterwards.
"Defaults" are highly personal but people who use schama's a lot will be impressed after first use

buckley

Hi Aaron,

I executed your instruction by reaming the folder in %localappdata% and indeed, I observed that Prompt created a fresh config folder.

But this did not resolve the issues I'm seeing.

To be 100% clear that what I see is not expected I recorded a video

https://www.dropbox.com/s/u9dj34metcdqb ... 46.7z?dl=0

Please change the first character of that file and replace 6z with 7z

It's a 7zip compressed mp4

I know its a hassle. if it's too much trouble I will find another way to get you the mp4. Its the way I get through the firewall...

Aaron L

Hi Tom,

It looks like the 7z file needs a password to decrypt?

I've just uploaded a new build (6.5.0.182) which should reduce the number of objects offered after qualifying with a database, could you check if using this new version helps at all?

Thanks,
Aaron.

buckley

Right, and there was even a password. I will send you a PM. Thanks for the effort Aaron

buckley

Hi Aaron,

The latest build 6.5.0.183 fixed it! Oh my, using intelliprompt across our +100 databases now... nerdgasm

Kind Regards, Tom

Aaron L

Haha, glad to hear it's sorted!