Way to preview (retroview) SQL queries of SQL Server Management Studio Express

Question

Could you tell me, please, if it's possible to preview (or at least retroview, for example, in a kind of a log file) SQL commands which SQL Server Management Studio Express is about to execute (or has just executed)?

In the past I used Embarcadero DBArtisan which shows SQL queries to be executed before actually running them on the server, so I am eager for this feature in Management Studio.

I have found an option "Auto generate change scripts", but it shows only DDL SQL queries (structure change), not data change.

Answer 1

You can use Query Profiler (from SQL) to view the queries and in your SQL Query embed in a transaction and at the end do a rollback.

BEGIN TRAN

 INSERT INTO Clients 
 SELECT 'Bruno', 'Alexandre';

END

ROLLBACK TRAN

when you rollback a transaction the process will go back to the begining of the BEGIN TRAN and you can be sure that nothing was made, this is the way to commit queries and to safe rollback if the SQL encounter an error...

like:

IF @@ERROR > 0 GOTO Error

:Error
ROLLBACK TRANSACTION

I hope that this is what you are looking for.

Answer 2

You can use

SET SHOWPLAN_TEXT ON

At the beginning of your batch to have the plan and text of the query echoed back to you. The query will not affect any rows while SHOWPLAN_TEXT is on.

You can then set SHOWPLAN_TEXT to OFF to run your query.

Hope this helps

Answer 3

To answer the question of viewing underlying queries executed when using Management Studio, there are two ways to do this.

Most of the Dialog boxes in Management Studio allow you to generate a change script to file or clipboard which can be useful for peaking under the hood, as well as applying changes between development, staging and production servers.

An alternate solution would be to run SQL Server Profiler, and filter by

• ApplicationName for Management Studio
• LoginName for your account

Some of the standard T-SQL trace templates would be OK, or if you make a custom trace template include the TextData field of the T-SQL SQL:BatchCompleted and SQL:BatchStarting Events

Hope this helps