PRO: Queries

All features described on this page are part of the Professional Edition.

While in beta, you can enable the Professional Edition by creating an account and downloading a license from https://portal.sqldsc.com.

The PRO edition will become a paid version. Until then, PRO features expire 60 days after the build date. Please see https://www.scalesql.com for updated builds or run sqldsc update.

Queries #

This is a BETA feature I am evaluating to see if it is useful

You can run a simple query against your targets with the following syntax:

sqldsc query dbcount.sql

assuming dbcount.sql contains the following

SELECT  @@SERVERNAME    AS [Server]
        ,COUNT(*)       AS [Databases]
FROM    sys.databases

that returns the following result

+-------------+-------------+-----------+
|    FQDN     |   SERVER    | DATABASES |
+-------------+-------------+-----------+
| D40\SQL2014 | D40\SQL2014 |         6 |
| D40\SQL2016 | D40\SQL2016 |        18 |
| D40\SQL2017 | D40\SQL2017 |         8 |
| D40\SQL2019 | D40\SQL2019 |        13 |
| D40\CASING  | D40\CASING  |         8 |
+-------------+-------------+-----------+

Folders #

Queries can be stored in the following folders

  • /config/queries
  • /config/queries-local
  • /config/queries-shared

The suggested use is that queries-local is added to .gitignore and most queries are stored there. Any queries that become broadly useful can be moved to queries-shared.

CSV Output #

The results can be piped to CSV with the following syntax:

sqldsc query dbcount.sql --csv dbs.csv

Parameters #

  • --eachdb parameter runs the query in each database on the server
  • --sort field_name orders the result set by field name. It handles numbers, strings, and datetimes. If it encounters any issues sorting it just returns unsorted results. This only sorts by a single field.
  • --descending will sort in descending order
  • --top n will only display the Top N results

Notes #

  • The FQDN comes from your target
  • Filtering targets by tags, names, and domains is supported
  • It runs sequentially at this time. It displays a handy little dot as each server is queried
  • This has only been tested with SELECT statements
  • It can’t return PRINT results
  • The .sql extension is assumed if you don’t include it