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 |
+-------------+-------------+-----------+

CSV Output #

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

sqldsc query dbcount.sql --csv dbs.csv

Notes #

  • The FQDN comes from your target
  • Filtering targets by roles, names, and domains is supported
  • It runs sequentially at this time. It displays a handy little dot as each server is queried
  • Starting in 1.42.4, the following subdirectories of config are searched for the query file: queries, queries-local, and queries-shared. The suggested use is that most queries are stored in queries-local which is added to .gitignore. Previously the query files were required to be in ./config/queries.
  • This has only been tested with SELECT statements
  • It can’t return PRINT results
  • The --eachdb parameter runs the query in each database on the server
  • The .sql extension is assumed if you don’t include it