I like big schemas and can not lie
You other DBAs can’t deny
That when studio opens up with a funky ass database
and a million objects in your face
It gets hung, your coding gets rough
’cause you notice your base was stuffed
Deep in the schema it’s searchin
I’m stuck and IntelliSense is lurchin
Oh baby, I wanna finish this query
But this lag is makin’ me weary
My co-workers tried to warn me
Cause this SQL rhyme got way to corny
So coders (Yeah!) Coders (Yeah!)
Can you code better than Hodor (Hell yeah!)
Then fire it up. Type it out.
Even SAN admins got to shout
DBA got back…….
to typing out queries because auto complete tools aren’t really working out
I have a love/hate relationship with some of my databases. I love hating them as they are great fodder for testing/breaking tools/procedures, blog posts and most importantly Sir Mix-A-Lot parodies. I’ve put up with IntelliSense stalling and even locking up SSMS when I’m writing a query against one of the 162 thousand tables containing 3.3 million columns. I know how to turn it off, but I don’t have the human RAM to hold the names of everything.
IntelliSense is far from perfect for me here so I tried out a few other options, Red Gate’s SQL Prompt and ApexSQL Complete. Both struggled in my setup and both had some very cool features. This is a hot take and not a review/recommendation, your mileage may vary. I haven’t reached out to support yet for either product or fully explorer all of their features.
Downloading a trial version and installing was a snap. It shows up as a tool bar and menu item. When you connect to a database SQL Prompt loads objects or at least it tries to. This is where I hit my first problem. The loading worked great on database with a small number of objects, but once I switched to a large db I ran into the following 2 errors, OutOfMemoryException and timeout. The timeout period is around 1 minute and I haven’t found a way to adjust this.
The prompting post errors were limited to the database level and it appeared that none of the tables had been loaded. I know the names of my databases and SSMS already has a cute little drop down to show this. I don’t think I need help at this level.
Promptless Prompt aside I like the Tab History, formatting and expanding wildcards. Expanding out my SELECT * when the table has over 200 columns is pretty great. Especially when those columns often use spaces or odd characters. I kid you not, I have a column that has both and & and ? IN THE NAME!!!
ApexSQL Complete Complete had the same issues with loading a large number of objects. SSMS returned a busy message and objects weren’t loaded. Once I started to write out my query Complete threw the error, “An error occurred while executing batch. Error message is: Exception of type ‘System.OutOfMemoryException’ was thrown.”
I uninstalled Apex without getting to deep into its features, but I did like Auto aliasing. On a small db Apex would automatically tack on an alias to your table. I once had a coworker who would alias their tables in alphabetical order. The first table would be “a”, the second “b” and so on a so forth. Apex would have been a vast improvement there.