Voice of the DBA

Tag: SQL Search

Adding SQL Search to Azure Data Studio

There are a limited number of extensions available for Azure Data Studio (ADS), but one that came out early is SQL Search. This is highlighted as a recommended extension from Microsoft. Redgate worked with them early on in the lifecycle of the product to get this extension working, including helping spec out the APIs for interaction with ADS.

My extensions pane is on the right, so my details are on the left. There’s a description of SQL Search as well as a link to install the extension. As with most extensions, the link takes you to a download page. For SQL Search, this is a Redgate Foundry page with a few builds. I picked the public one.

Installing extensions is basically using File | Install Extensions from VSIX and picking the downloaded file. You need to approve the extension and then reload once it’s installed. You can see it installed if you re-open the extensions pane.

Using SQL Search

To use this extension, you can use the CTRL+Shift+P command to bring up the command palette. When you do this, you get a way to run commands at the top of the ADS window. This is a handy tool that you will use often.

If we type “SQL S”, we’ll get the SQL Search commands. There is a reindex command and a search command. These are the two items implemented at this time.

The re-index command will update the SQL Search index with information from the current database connection. If you cannot find an object you need, then you might run the reindex.

The other command allows you to search the database. If we click this one, we get a new edit box at the top of the window. Focus will be in this window, so you can type a term. For me, I know there are a number of items in this database for blogs, so let’s enter that.

The results will appear in a new tab alongside your currently open tabs.

The results show me that I have an object name along with the schema and database. We see the type of the object and then the way the object was matched. Next to the object name, there is a circle with an ellipsis. If we click this, we have a couple of choices for what to do with our results.

We can highlight this object in our object explorer, which will, well, do nothing for me. I suspect that the call from the extension to the servers Object Explorer is flaky. Even if I open the Servers pane and then click “Reveal in Object Explorer”, nothing happens. I see whatever I had opened in the Servers Pane.

If I close the server pane and instead click View Definition, I get a split pane on the right with the object definition. I can resize this as needed, but this is a view only pane. I can’t edit or execute this code. It’s for reference, which can be helpful when coding, but is of limited use.

I can copy this code for another window if I click the start and end places in the search results while holding the Shift key. Then I right click in the window and select Copy.

This is a limited use extension, but it does allow one to find code quicker than browsing through the Object Explorer. We can keep the search results up as a separate pane, so that we can use this as a pick list if we were looking for all the dependent objects for an object.

One nice touch from ADS is that if you CTRL+Shift+P again, the SQL Search commands appear at the top as recently used items.

Give SQL Search a try and let us know what you think.

November 2, 2018
Learning to Search

I’ve had my own domain for about 17 years now, and about 6 or 7 years I decided to move my email from GoDaddy to Google. There were a variety of reasons, but the bottom line was I got more accounts, more space, for less money. I’ve been happy there, and I even set up accounts for my kids to have their own email.

About a year after I gave my son an email, I went to ask him if he’d seen a particular note from our Scout troop. He opened email and I saw that he had quite a full inbox. He had various newsletters and kid related emails (most unread) as well as a bunch of different messages. As he looked for the email, I asked him why he didn’t delete the old messages that he was ignoring or had already read. His reply surprised me.

He pointed to the left side of the screen, below the list of folders, at a small graph. “See that,” he said, pointing at the percentage of space he’d used. “When that goes above 10%, I’ll worry about deleting things.” It was at 1% at that time, after a year of email.

Not long after that, I read a note from Mark Cuban on email, where he said he didn’t bother to delete emails or move them into folders or anything else. It’s inefficient to worry about this. Instead, he buys space as needed and uses search. I started doing that as well, rarely deleting emails, and counting on search to help me find things. I’m at 5% of my Google allocation after 6 years and if something isn’t on the first page of email, I always use search to find it.

I’ve started to do that elsewhere. Even in databases. I was reminded a few months ago that Redgate Software (my employer) makes SQL Search. Ike Ellis made a short video that shows how he uses SQL Search to find objects in databases rather than wandering through the Object Explorer. Ike is a consultant and runs into lots of databases and can’t remember where every object is in all of them.

I like that, and I find myself starting to use SQL Search because it’s quicker than opening a database, then opening Programmability, then opening Stored Procedures, and scrolling. Search is a couple of clicks and keystrokes, and way faster.

I use search more and more, on my local disks, inside particular websites (you should all know about site: searches on search engines), in Books Online, and in code. Most IDEs make this easy, and trust me, once you start to do this, you’ll never go scrolling around again.

At least not if you want to be efficient.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.9MB) podcast or subscribe to the feed at iTunes and Mevio .

July 27, 2016
A Better (Free) Search in SSMS

A few years back, Redgate Software released SQL Search. This was an internal project that became a free product we released. Free as in beer, so go get it.

Why use SQL Search?

There are a number of good reasons, which Ike Ellis of Crafting Bytes does a good job of highlighting in a short video. In short, SQL Search is much more robust in helping you track down objects.

I’m sure plenty of you are like me. You think you know where most of the objects live in a database. I’ll use the SQLServerCentral database as an example. If I want to look in the forums for how to modify profiles, I know there’s an InstantASP_Users table. I can quickly go to the Object Explorer, hit Databases, go to SQLServerCentral_Forums, hit tables, and pick the table. I can get the designer, a list of columns, etc. This is a familiar way of working with databases.

However, I’d be lying if I said I knew all of the objects that referenced that table. Especially stored procedures. Even if I work with a database every day, and I have a great memory, I don’t always keep every reference in mind.

I still follow that process (too often), but I’ve learned there’s a better way of working with a database.

SQL Search.

If I type “asp_user” in the search dialog, I get this:

Now I can see lots of objects, including those in other databases. If I know I want tables, I can easily filter.

However I don’t just get the names, I can get some details:

The more I move to search, the more I start to realize that the Object Explorer is quite slow, and inefficient. I’d be better served by just going to search right away. At least, a search that gives me plenty of useful information,

So download SQL Search today and see how it can make development easier in large databases, and check out Ike’s video.

June 15, 2016