當執行帶有多個命令的腳本時，如何找出我們在哪裡？

我有一些流程需要在開始之前採取不同的步驟。例如：

禁用外鍵檢查約束：

select 'alter table '+fk.table_schema+'.'+fk.table_name
+' NOCHECK CONSTRAINT '+fk.CONSTRAINT_NAME 
from SAProduct.information_schema.table_constraints fk 
join SAProduct.information_schema.tables t
on t.table_schema = fk.table_schema 
and t.table_name = fk.table_name 
WHERE constraint_type='FOREIGN KEY'

這將產生一個很大的語句列表，我只複製 3 個：

use saproduct
go
alter table dbo.tblProdClassificationCode NOCHECK CONSTRAINT FK_tblProdClassificationCode_tblProdClassification
alter table dbo.tblProdClassificationCodeDescr NOCHECK CONSTRAINT fk_ProdClassificationCodeDescr_ProdClassificationCode
alter table dbo.tblProdClassificationCodeGenerate NOCHECK CONSTRAINT fk_ProdClassificationCodeGenerate_ProdClassification

如果我要一次性執行它，我怎麼知道我在哪裡？我正在處理哪張桌子，下一張是哪一張？

刪除也是一樣的：

select 'DELETE FROM '+table_schema+'.'+table_name
from saproduct.information_schema.tables 
where table_name like 'tbl%'

上面的腳本會產生下面的一組語句：

use saproduct
go
DELETE FROM dbo.tblBLanguage
DELETE FROM dbo.tblBLgCategoryXRef
DELETE FROM dbo.tblBLgSegmentXRef
DELETE FROM dbo.tblProdClassification
DELETE FROM dbo.tblProdClassificationCode
DELETE FROM dbo.tblProdClassificationCodeDescr
DELETE FROM dbo.tblProdClassificationCodeGenerate
DELETE FROM dbo.tblProdClassificationDescr
DELETE FROM dbo.tblProdClassificationMarket
DELETE FROM dbo.tblProdClassificationTier
DELETE FROM dbo.tblProdClassificationValue
DELETE FROM dbo.tblProdData
DELETE FROM dbo.tblProdDataDescr
DELETE FROM dbo.tblProdDataMarket
DELETE FROM dbo.tblProdDataTempTier1Descr
DELETE FROM dbo.tblProdDataTempTier1Tier2Descr
DELETE FROM dbo.tblProdDataTier
DELETE FROM dbo.tblProdDataValue
DELETE FROM dbo.tblProdDataValueDate
DELETE FROM dbo.tblProdDataValueNumber
DELETE FROM dbo.tblProdDataValueString
DELETE FROM dbo.tblProdName
DELETE FROM dbo.tblProdNameSizeAlias
DELETE FROM dbo.tblProdNameSizeRuleAlias
DELETE FROM dbo.tblProdNameStructure
DELETE FROM dbo.tblProdNameStructureDescr
DELETE FROM dbo.tblProduct

我要刪除的下一張表是哪一張？

我試圖以某種順序訪問表以最大限度地減少死鎖，特別是在必須放置任何類型的排他鎖時。它不在這裡，但我也盡量避免鎖升級，所以它會變得複雜。我喜歡“看”到底發生了什麼。我正在使用 SQL Server 2014：

Microsoft SQL Server 2014 - 12.0.2000.8 (X64) 
   Feb 20 2014 20:04:26 
   Copyright (c) Microsoft Corporation
   Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

我同意 Aaron的觀點，這RAISERROR...WITH NOWAIT可能非常有用，如果您可以完全控制正在生成的腳本，這可能是可行的方法。

但是，如果目前正在執行一個長腳本並且您無法更改腳本以添加RAISERROR呼叫，那麼獲取此資訊的直接方法也較少。

測試腳本

這是一個測試腳本，您可以執行它來幫助展示以下兩種方法：

SELECT 1
WAITFOR DELAY '00:00:15'
SELECT 2
WAITFOR DELAY '00:00:15'
SELECT 3

sp_whosiactive

執行此腳本時，您可以使用sp_whoisactive查看目前的伺服器活動。您通常可以查看目前正在執行的特定語句的查詢計劃。就我而言，我看到以下內容，因為該WAITFOR語句最有可能在任何給定時刻執行：

使用 sys.dm_exec_requests.statement_start_offset

或者，Conor Cunningham 也有一篇關於從AND中提取語句的文章。我不相信這已被納入，但您可以使用如下查詢來查看目前正在執行的語句和整個批處理。sys.dm_exec_query_stats``sys.dm_exec_sql_text``sp_whoisactive

SELECT er.session_Id AS spid
   --Use the full batch text and the start/end offset of the currect statement to figure 
   --out the SQL that is currently executing. This logic is based on the blog post above
   --but has been updated in light of strange cases in SQL Server that caused the original
   --blog post logic to crash with out of bounds errors on the SUBSTRING operation.
   , SUBSTRING (qt.text 
               , (CASE WHEN er.statement_start_offset > DATALENGTH(qt.text) 
                   THEN 0 ELSE er.statement_start_offset/2 END)+1
               , (CASE WHEN er.statement_end_offset <= 0 THEN DATALENGTH(qt.text)
                   ELSE er.statement_end_offset 
                   END - CASE WHEN er.statement_start_offset > DATALENGTH(qt.text) 
                       THEN 0 ELSE er.statement_start_offset/2 END)
                   + 1
               ) AS query
   , qt.text AS parent_query
FROM sys.dm_exec_requests er
JOIN sys.dm_exec_sessions s
   ON s.session_id = er.session_id
   AND s.session_id <> @@SPID      -- Ignore this current statement.
   AND s.is_user_process = 1       -- Ignore system spids.
   AND s.program_name NOT LIKE '%SQL Server Profiler%' -- Ignore profiler traces
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
ORDER BY spid

引用自：https://dba.stackexchange.com/questions/115765