SQL語法提示工具SQL Prompt使用教程：使用SQL Prompt代碼段執(zhí)行重復(fù)任務(wù)（下）

---

SQL Prompt根據(jù)數(shù)據(jù)庫的對象名稱、語法和代碼片段自動進(jìn)行檢索，為用戶提供合適的代碼選擇。自動腳本設(shè)置使代碼簡單易讀--當(dāng)開發(fā)者不大熟悉腳本時(shí)尤其有用。SQL Prompt安裝即可使用，能大幅提高編碼效率。此外，用戶還可根據(jù)需要進(jìn)行自定義，使之以預(yù)想的方式工作。

點(diǎn)擊下載SQL Prompt正式版

本教程發(fā)現(xiàn)了使用SQL Prompt代碼段消除重復(fù)執(zhí)行各種任務(wù)的樂趣，這些任務(wù)從插入注釋標(biāo)頭、創(chuàng)建表到執(zhí)行有用的元數(shù)據(jù)查詢。本文為該教程后半部分內(nèi)容，內(nèi)容緊接前文！

---

用于創(chuàng)建對象的代碼片段

每當(dāng)我創(chuàng)建數(shù)據(jù)庫對象時(shí)，無論出于何種原因，該任務(wù)通常都會包含一些“重復(fù)元素”。例如，當(dāng)我創(chuàng)建一個(gè)表時(shí)，它幾乎總是具有某些共同的特征，或者每次執(zhí)行任務(wù)時(shí)（例如，提取最近添加到表中的所有行以進(jìn)行報(bào)告），我經(jīng)常需要相同的對象集。

一旦我開始思考我經(jīng)常執(zhí)行的任務(wù)，有關(guān)片段的想法就開始堆積起來！

更有用的“創(chuàng)建表格”代碼段

當(dāng)然，我創(chuàng)建的每個(gè)新表的名稱和總體結(jié)構(gòu)都不同，但是我們創(chuàng)建的每個(gè)表都包含以下共同要求：

• 代理主鍵
• 一列指示何時(shí)創(chuàng)建每一行，一列指示何時(shí)修改它–這些列中的每一個(gè)都會有DEFAULT約束，其中包括表名和列名
• PAGE 通常將為表啟用壓縮

在更完整的解決方案中，我可能包含代碼以查看表是否已存在、刪除表的現(xiàn)有版本、添加擴(kuò)展屬性等等。為了為這些常用表功能創(chuàng)建標(biāo)準(zhǔn)模板，并從任務(wù)中刪除重復(fù)元素，我創(chuàng)建了自定義的createtable代碼段，如下所示：
名稱：createtable 
說明：LD-Base表使用我們的標(biāo)準(zhǔn)項(xiàng)目創(chuàng)建，并預(yù)先填充了
代碼段代碼：

CREATE TABLE $SchemaName$.$TableName$
(
    $TableName$id         int        NOT NULL CONSTRAINT PK$TableName$ PRIMARY KEY,
    RowCreatedTime      datetime2(0) NOT NULL CONSTRAINT DFLT$TableName$$RowCreatedTime DEFAULT(SYSDATETIME()),
    RowLastModifiedTime datetime2(0) NOT NULL CONSTRAINT DFLT$TableName$$RowLastModifiedTime DEFAULT(SYSDATETIME())
)
WITH (DATA_COMPRESSION = PAGE);

占位符為$SchemaName$和$TableName$，后者重復(fù)多次。調(diào)用代碼段，輸入架構(gòu)名稱，然后開始輸入表名稱，在這種情況下，我將其稱為NewTable：

當(dāng)您編輯一個(gè)占位符實(shí)例時(shí)，它們?nèi)慷紩淖儯⊥瓿奢斎朊Q，然后按Enter：

我可能不會按原樣使用代理鍵，但是我肯定會在表上有一個(gè)主鍵，它將根據(jù)此代碼段中建立的標(biāo)準(zhǔn)進(jìn)行命名。在我創(chuàng)建的大多數(shù)表中，我還將需要RowCreatedTime和RowLastModifiedTime列。在過去的五年中，我以相同的模式手動創(chuàng)建了數(shù)百個(gè)（即使不是數(shù)千次）默認(rèn)約束。

自然地，創(chuàng)建表的其余大部分并不是公式化的，無法做成一個(gè)片段，但是該片段提供的是通用的重復(fù)部分的通用框架。我還創(chuàng)建了列摘要，它們將定義必要的DEFAULT，CHECK或FOREIGN KEY約束及其命名約定（通常包括表名，也許還包括列名）。了解產(chǎn)品信息，請咨詢

監(jiān)視表中創(chuàng)建的行的代碼段

下一個(gè)示例來自一個(gè)項(xiàng)目，在該項(xiàng)目中，我必須準(zhǔn)備關(guān)于一組表上發(fā)生的新活動的每日報(bào)告。對于每個(gè)表，這需要幾個(gè)存儲過程來獲取并保存在特定日期之后添加到基表中的行，以及一個(gè)用于存儲結(jié)果以供報(bào)告的“ UsedRows ”表。

對于代碼段而言，這是另一項(xiàng)完美的任務(wù)，因?yàn)楸M管替代鍵的名稱和數(shù)據(jù)類型可能會發(fā)生變化，但我們基本上使用同一組對象來獲取每個(gè)表的數(shù)據(jù)。

這是代碼段的代碼：

請注意，這些代碼片段足夠聰明，可以處理在對象名稱和字符串中使用$字符，這是我個(gè)人命名風(fēng)格的一個(gè)功能。 參數(shù)名稱的名稱字符中不得包含空格，但是，如果命名標(biāo)準(zhǔn)要求多個(gè)$字符而不包含空格字符，則可能會出現(xiàn)問題。了解產(chǎn)品信息，請咨詢

執(zhí)行實(shí)用程序代碼的工具

當(dāng)我開始研究如何使用代碼片段時(shí)，想到的第一件事就是使用它們來執(zhí)行我經(jīng)常執(zhí)行的代碼，這些代碼是從源代碼控制中保存的文件中執(zhí)行的，通常用于回答有關(guān)實(shí)例狀態(tài)或元數(shù)據(jù)的問題。我想出了兩種方法來做到這一點(diǎn)，并且我都成功地運(yùn)用了這兩種方法。

• 在代碼段中使用SQLCMD：r命令從源代碼管理中存儲的文件列表中訪問并執(zhí)行指定的.sql文件
• 將所需的SQL捕獲到一個(gè)片段中，以便您可以獲取代碼，根據(jù)需要對其進(jìn)行修改并執(zhí)行它

用于特定腳本的哪種方法取決于您每次運(yùn)行代碼時(shí)需要對代碼進(jìn)行更改的可能性。

SQLCMD“剪貼板”代碼段

因此，假設(shè)您有一整套有用的元數(shù)據(jù)查詢，所有這些查詢都可以“按原樣”運(yùn)行，并且每個(gè)查詢都存儲在變更管理系統(tǒng)（GitHub，Dropbox等）中的單獨(dú)文件中，并且與團(tuán)隊(duì)共享。作為一個(gè)非常簡單的示例，其中一個(gè)可能是一個(gè)名為CheckLoginSystemCount.sql的文件，該文件報(bào)告了SQL Server 的登錄計(jì)數(shù)。

SELECT login_name, COUNT(session_id) AS session_count
FROM sys.dm_exec_sessions
GROUP BY login_name;

我不需要在每個(gè)文件中四處尋找，而是創(chuàng)建了一個(gè)片段，該片段實(shí)際上只是SQLCMD :r命令的列表，每個(gè)都指向包含這些元數(shù)據(jù)查詢之一的文件。

名稱：querylist 
說明：LD-到常用元數(shù)據(jù)查詢的鏈接
片段代碼：

/******************************************************
Execute queries stored in file system. Use SQLCMD mode to run
******************************************************/
--Use EXIT to make sure you don't accidentally execute everything. EXIT
--in SQLCMD mode will stop processing
EXIT
 
--Check login count
:R E:\Dropbox\Projects\PerformanceTools\CheckLoginSystemCount.sql
EXIT
 
--List locks on the instance
:R E:\Dropbox\Projects\PerformanceTools\ListInstanceLocks.sql
EXIT
--Return size of current database files
:R E:\Dropbox\Projects\Catalog\ListDatabaseConfigurations.sql
EXIT
--Other queries...
EXIT

現(xiàn)在，當(dāng)您調(diào)用該代碼片段時(shí)，在設(shè)置為SQLCMD模式的SSMS查詢窗格中，您將獲得文件列表。只需突出顯示:R指向您要運(yùn)行的文件的行，它將執(zhí)行文件中的內(nèi)容。這些EXIT行確保您不會意外執(zhí)行每個(gè)腳本。

調(diào)查鎖活動的代碼段

我有幾個(gè)相當(dāng)復(fù)雜的DMV查詢，它們經(jīng)常運(yùn)行以調(diào)查數(shù)據(jù)庫實(shí)例上的當(dāng)前活動。例如，我有一個(gè)查詢來查看數(shù)據(jù)庫中當(dāng)前持有的鎖。它取自我和Tim Ford十年多以前寫的關(guān)于DMV的書（盡管它有一些很好的查詢?nèi)匀皇窍嚓P(guān)的）。

同樣，我曾經(jīng)在源代碼管理中將它存儲在一個(gè)文件中，并根據(jù)需要將其搜索出來。90%的情況下，我可以“按原樣”運(yùn)行它，但是對于其他10%，我需要添加過濾器，選擇不同的列，等等。令人惱火的是，當(dāng)我關(guān)閉窗口時(shí)，我總是保存對原始文件的更改(該文件適用于90%的用例!)

我認(rèn)為創(chuàng)建包含查詢的代碼片段比較容易，也許可以使用代碼片段參數(shù)來簡化某些參數(shù)的值，然后再運(yùn)行它。在我的示例中，我為資源類型和SPID設(shè)置了占位符，兩者均在代碼段中默認(rèn)設(shè)置為返回所有值。

名稱：locklist 
說明：LD-用于獲取當(dāng)前數(shù)據(jù)庫中連接的鎖
代碼片段：

現(xiàn)在，我可以調(diào)用鎖定列表代碼片段，執(zhí)行查詢，提供過濾器或讓它們使用默認(rèn)值。如果保存查詢，則不會覆蓋模板，并且在提交代碼時(shí)，此更改是否重要沒有問題。

請注意，將這樣的復(fù)雜查詢提交給源代碼管理仍然很有價(jià)值。對于除了最簡單的代碼段以外的所有代碼段，代碼段管理器都不應(yīng)該是基礎(chǔ)查詢的唯一副本。了解產(chǎn)品信息，請咨詢

總結(jié)

片段是硬核程序員通常不好稱呼的那些事情之一，他們主要認(rèn)為它們太瑣碎，不值得花時(shí)間。直到我考慮了如何使用它們來自動化一些重復(fù)性任務(wù)后，我才意識到它們比我最初預(yù)期的更有價(jià)值。

我已經(jīng)演示了幾個(gè)簡單但省時(shí)的代碼片段，這些代碼片段可以消除多余的擊鍵或創(chuàng)建對象，或者可以輕松地重用源代碼控制文件中存儲的代碼，從而使編碼變得更容易。

相關(guān)內(nèi)容推薦：

SQL語法提示工具SQL Prompt使用教程：使用SQL Prompt代碼段執(zhí)行重復(fù)任務(wù)（上）
SQL Prompt使用教程>>>

---

想要購買SQL Prompt正版授權(quán)，或了解更多產(chǎn)品信息請點(diǎn)擊