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)?
你能告诉我,是否可以预览(或者至少是retroview,例如,在一种日志文件中)SQL Server Management Studio Express即将执行(或刚刚执行)的SQL命令?
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.
在过去,我使用了Embarcadero DBArtisan,它显示了在服务器上实际运行它们之前要执行的SQL查询,因此我渴望在Management Studio中使用此功能。
I have found an option "Auto generate change scripts", but it shows only DDL SQL queries (structure change), not data change.
我找到了一个选项“自动生成更改脚本”,但它只显示了DDL SQL查询(结构更改),而不是数据更改。
3 个解决方案
#1
2
To answer the question of viewing underlying queries executed when using Management Studio, there are two ways to do this.
要回答查看使用Management Studio时执行的基础查询的问题,有两种方法可以执行此操作。
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.
Management Studio中的大多数对话框允许您生成文件或剪贴板的更改脚本,这些脚本可用于高峰,以及在开发,登台和生产服务器之间应用更改。
An alternate solution would be to run SQL Server Profiler, and filter by
另一种解决方案是运行SQL Server Profiler,并进行过滤
- ApplicationName for Management Studio
- LoginName for your account
Management Studio的ApplicationName
您帐户的LoginName
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
一些标准的T-SQL跟踪模板可以正常,或者如果您创建自定义跟踪模板,则包括T-SQL SQL的TextData字段:BatchCompleted和SQL:BatchStarting Events
#2
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.
您可以使用Query Profiler(来自SQL)查看查询,并在嵌入事务的SQL查询中,最后执行回滚。
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...
当你回滚事务时,进程将回到BEGIN TRAN的开头,你可以确定没有做任何事情,如果SQL遇到错误,这是提交查询和安全回滚的方法......
like:
IF @@ERROR > 0 GOTO Error
:Error
ROLLBACK TRANSACTION
#3
0
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.
在批处理开始时,让查询的计划和文本回显给您。启用SHOWPLAN_TEXT时,查询不会影响任何行。
You can then set SHOWPLAN_TEXT to OFF to run your query.
然后,您可以将SHOWPLAN_TEXT设置为OFF以运行查询。
#1
2
To answer the question of viewing underlying queries executed when using Management Studio, there are two ways to do this.
要回答查看使用Management Studio时执行的基础查询的问题,有两种方法可以执行此操作。
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.
Management Studio中的大多数对话框允许您生成文件或剪贴板的更改脚本,这些脚本可用于高峰,以及在开发,登台和生产服务器之间应用更改。
An alternate solution would be to run SQL Server Profiler, and filter by
另一种解决方案是运行SQL Server Profiler,并进行过滤
- ApplicationName for Management Studio
- LoginName for your account
Management Studio的ApplicationName
您帐户的LoginName
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
一些标准的T-SQL跟踪模板可以正常,或者如果您创建自定义跟踪模板,则包括T-SQL SQL的TextData字段:BatchCompleted和SQL:BatchStarting Events
#2
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.
您可以使用Query Profiler(来自SQL)查看查询,并在嵌入事务的SQL查询中,最后执行回滚。
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...
当你回滚事务时,进程将回到BEGIN TRAN的开头,你可以确定没有做任何事情,如果SQL遇到错误,这是提交查询和安全回滚的方法......
like:
IF @@ERROR > 0 GOTO Error
:Error
ROLLBACK TRANSACTION
#3
0
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.
在批处理开始时,让查询的计划和文本回显给您。启用SHOWPLAN_TEXT时,查询不会影响任何行。
You can then set SHOWPLAN_TEXT to OFF to run your query.
然后,您可以将SHOWPLAN_TEXT设置为OFF以运行查询。