PRO: Queries (BETA)

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

You can purchase licenses for the Professional Edition on the Portal: https://portal.sqldsc.com.

Queries (BETA) #

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 dates, and times. If it encounters any issues sorting it just returns unsorted results. This only sorts by a single field. (You may wonder why this isn’t --orderby. My hands can’t type orderby without a space. After much annoyance, it became --sort)
  • --desc 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