The Multi Query tool helps you easily query multiple SQL Server instances and databases across your enterprise. With this tool, you can run T-SQL scripts to perform any command supported by SQL Server. For example, you can compare data points across instances or deploy a schema change across databases.
The Multi Query tool helps you:
- Run ad-hoc queries to make quick changes on multiple instances and databases.
- Run existing queries to perform routine tasks or schema maintenance.
- Use queries to compare data points from multiple instances and databases.
- Create and reuse target lists of the instances and databases on which a query should act.
- Save the returned result sets to track statistics or object properties over time.
How the Multi Query tool works
Run the Multi Query tool on your instances and select a database. If you do not specify a database, the Multi Query tool connects to and runs the query on the default database configured in SQL Server.
Use the Query Options to narrow the scope of the returned result set and improve performance when running queries that return more results than you expected, are run against many different targets, or contain complex functions. You can search targeted databases using a wildcard. The Add Query Target dialog includes fields that allow a user to enter a search term or expression to filter for a list of matching databases.
By default, Multi Query connects to the specified SQL Server instances using the credentials of your currently logged on Windows account, and then runs the specified query against those instances.
Use the Multi Query tool
To create a query target list using the Multi Query tool:
- Open the IDERA SQL Admin Toolset Launchpad, and then click Multi Query.
- On the Query Targets task bar, click Add.
- Search for and then select the SQL Server instance or server group that you want to query. To specify multiple SQL Servers or server groups, separate each name with a semicolon.
- Select the authentication type you want the Multi Query tool to use to connect to these instances.
- Optional. Click Test Connection to verify that you can connect using the entered authentication specifics.
- Indicate whether you want the Multi Query tool to act on the default database (as defined in SQL Server) or a specific database. You can filter the list of databases using a wildcard.
- Click OK.
- On the Query Targets task bar, click Save. Consider specifying a name that correlates with the queries you intend to run against this list of targets.
To run a query using the Multi Query tool:
- In the Query Targets pane, specify which instances and databases that you want the query to target. You can specify new targets by clicking Add, or use an existing target by clicking Open, and then searching for and selecting the appropriate target.
- In the Query pane, enter the query you want to run. You can type an ad-hoc query in the provided text pane, or run an existing script by clicking Open, and then searching for and selecting the appropriate script.
- If you want to run your query against all the listed targets , click Execute Query. If you want to run your query against specific targets , click Execute Against Selected Query Targets.
- View the query status and results sets. You can:
- View the query status using the Summary tab.
- Combine result sets that contain the same columns using the Combined Results tab.
- View individual result sets for a specific target.
- Maximize the pane by clicking Full Screen.
- If you want to save the results , copy the list to clipboard, or export the list as an XML or CSV file.