Executing queries on all Azure databases with PowerShell

Executing queries on all Azure databases with PowerShell

Some time ago I found that some test databases were deployed from a dump that contained no indexes. It was a bit more than twenty Azure SQL Databases spanned across two instances.

I needed an easy way of executing a simple query (counting the number of indexes) on all databases on the server. The problem was that in the Azure use statement could not be… used.

I checked couple of tools but they simply applied use to the existing query. Furthermore use of Azure DB Elastic Query was too complex for such simple task.

Why not solve this with PowerShell then?

The script needed to do couple of things:

  • Ask for credentials to access the server.
  • Grab a list of all databases.
  • Query each database and output the result in a clean way.

After a bit of fiddling I decided to add some more features:

  • Specify the query in a command line.
  • If the query is too complex to use as a parameter it can be inserted into the scripts body.
  • The query inserted in the scripts body will always override one passed as parameter.
A bit of disclaimer here. The script is presented “as is” and for me it worked fine, but your mileage may vary. It should drop no tables or databases unless you specifically tell it to and it queries the master DB as well. Also in future the script will most likely be updated.

Leave me a comment if you’d like to suggest improvements or fixes. Thanks.

Related Posts

Leave a reply