使用 SQL Server Management Studio (SSMS) 的提示和技巧Tips and tricks for using SQL Server Management Studio (SSMS)

03/13/2018

本文內容

本文介紹了一些使用 SQL Server Management StudioSQL Server Management Studio (SSMS) 的提示和技巧。This article gives you some tips and tricks for using SQL Server Management StudioSQL Server Management Studio (SSMS). 本文介紹如何：This article shows you how to:

注釋/取消注釋 Transact-SQL (T-SQL) 文本Comment/uncomment your Transact-SQL (T-SQL) text

縮進文本Indent your text

在對象資源管理器中篩選對象Filter objects in Object Explorer

訪問 SQL ServerSQL Server 錯誤日志Access your SQL ServerSQL Server error log

查找 SQL ServerSQL Server 實例的名稱Find the name of your SQL ServerSQL Server instance

先決條件Prerequisites

若要測試本文提供的步驟，必須有 SQL Server Management StudioSQL Server Management Studio、對 SQL Server 的訪問權限，以及 AdventureWorks 數據庫。To test out the steps provided in this article, you need SQL Server Management StudioSQL Server Management Studio, access to a SQL server, and an AdventureWorks database.

注釋/取消注釋 T-SQL 代碼Comment/uncomment your T-SQL code

可使用工具欄中的“注釋”按鈕注釋和取消注釋部分文本 。You can comment and uncomment portions of your text by using the Comment button on the toolbar. 系統不會執行已注釋掉的文本。Text that is commented out is not executed.

打開 SQL Server Management StudioSQL Server Management Studio。Open SQL Server Management StudioSQL Server Management Studio.

連接到 SQL Server。Connect to your SQL server.

打開“新建查詢”窗口。Open a New Query window.

將以下 Transact-SQLTransact-SQL 代碼粘貼到文本窗口。Paste the following Transact-SQLTransact-SQL code in your text window.

USE master

GO

-- Drop the database if it already exists

IF EXISTS (

SELECT name

FROM sys.databases

WHERE name = N'TutorialDB'

)

DROP DATABASE TutorialDB

GO

CREATE DATABASE TutorialDB

GO

ALTER DATABASE [TutorialDB] SET QUERY_STORE=ON

GO

突出顯示文本的“更改數據庫”部分，然后選擇工具欄中的“注釋”按鈕 ：Highlight the Alter Database portion of the text, and then select the Comment button on the toolbar:

選擇“執行”運行取消注釋的文本部分 。Select Execute to run the uncommented portion of the text.

突出顯示除“更改數據庫”命令之外的所有內容，然后選擇“注釋”按鈕 ：Highlight everything except for the Alter Database command, and then select the Comment button:

備注

注釋的文本的鍵盤快捷方式是 CTRL + K，CTRL + C 。The keyboard shortcut to comment text is CTRL + K, CTRL + C.

突出顯示文本的“更改數據庫”部分，然后選擇工具欄中的“取消注釋”按鈕以取消注釋 ：Highlight the Alter Database portion of the text, and then select the Uncomment button to uncomment it:

備注

取消注釋的文本的鍵盤快捷方式是 CTRL + K，CTRL + U 。The keyboard shortcut to uncomment text is CTRL + K, CTRL + U.

選擇“執行”運行取消注釋的文本部分 。Select Execute to run the uncommented portion of the text.

縮進文本Indent your text

可使用工具欄上的縮進按鈕增加或減少文本的縮進。You can use the indentation buttons on the toolbar to increase or decrease the indent of your text.

打開“新建查詢”窗口。Open a New Query window.

將以下 Transact-SQLTransact-SQL 代碼粘貼到文本窗口：Paste the following Transact-SQLTransact-SQL code in your text window:

USE master

GO

--Drop the database if it already exists

IF EXISTS (

SELECT name

FROM sys.databases

WHERE name = N'TutorialDB'

)

DROP DATABASE TutorialDB

GO

CREATE DATABASE TutorialDB

GO

ALTER DATABASE [TutorialDB] SET QUERY_STORE=ON

GO

突出顯示文本的“更改數據庫”部分，然后選擇工具欄上的“增加縮進”按鈕以向前移動此文本 ：Highlight the Alter Database portion of the text, and then select the Increase Indent button on the toolbar to move this text forward:

再次突出顯示文本的“更改數據庫”部分，然后選擇“減少縮進”按鈕以向后移動此文本 。Highlight the Alter Database portion of the text again, and then select the Decrease Indent button to move this text back.

在對象資源管理器中篩選對象Filter objects in Object Explorer

在具有多個對象的數據庫中，可以使用篩選功能來搜索特定表、視圖等。本節介紹如何篩選表，但可在對象資源管理器中的任何其他節點中使用以下步驟：In databases that have many objects, you can use filtering to search for specific tables, views, etc. This section describes how to filter tables, but you can use the following steps in any other node in Object Explorer:

連接到 SQL Server。Connect to your SQL server.

展開“數據庫” > “AdventureWorks” > “表” 。Expand Databases > AdventureWorks > Tables. 此時將顯示數據庫中的所有表。All the tables in the database appear.

右鍵單擊“表”，然后選擇“篩選器” > “篩選器設置” ：Right-click Tables, and then select Filter > Filter Settings:

在“篩選器設置”窗口中，可以修改以下某些篩選器設置 ：In the Filter Settings window, you can modify some of the following filter settings:

按名稱篩選：Filter by name:

按架構篩選：Filter by schema:

若要清除篩選器，請右鍵單擊“表”，然后選擇“刪除篩選器” 。To clear the filter, right-click Tables, and then select Remove Filter.

訪問 SQL Server 錯誤日志Access your SQL Server error log

錯誤日志是一個文件，其中包含 SQL ServerSQL Server 實例中所發生操作的相關詳細信息。The error log is a file that contains details about things that occur in your SQL ServerSQL Server instance. 可瀏覽和查詢 SSMS 中的錯誤日志。You can browse and query the error login SSMS. 錯誤日志是位于磁盤上的日志文件。The error log is a .log file that's located on your disk.

在 SSMS 中打開錯誤日志Open the error log in SSMS

連接到你的 SQL ServerSQL Server。Connect to your SQL ServerSQL Server.

展開“管理” > “SQL Server 日志” 。Expand Management > SQL Server Logs.

右鍵單擊“當前”錯誤日志，然后選擇“查看 SQL Server 日志” ：Right-click the Current error log, and then select View SQL Server Log:

在 SSMS 中查看查詢日志Query the error log in SSMS

連接到 SQL Server。Connect to your SQL server.

打開“新建查詢”窗口。Open a New Query window.

將以下 Transact-SQLTransact-SQL 代碼粘貼到查詢窗口：Paste the following Transact-SQLTransact-SQL code in your query window:

sp_readerrorlog 0,1,'Server process ID'

將單引號中的文本修改為要搜索的文本。Modify the text in the single quotes to text you want to search for.

執行查詢然后查看結果：Execute the query, and then review the results:

如果連接到 SQL Server，請查找錯誤日志位置Find the error log location if you're connected to SQL Server

連接到你的 SQL ServerSQL Server。Connect to your SQL ServerSQL Server.

打開“新建查詢”窗口。Open a New Query window.

將以下 Transact-SQLTransact-SQL 代碼粘貼到查詢窗口，然后選擇“執行”：Paste the following Transact-SQLTransact-SQL code in your query window, and then select Execute:

SELECT SERVERPROPERTY('ErrorLogFileName') AS 'Error log file location'

結果將顯示文件系統中錯誤日志的位置：The results show the location of the error log in the file system:

如果無法連接到 SQL Server，請查找錯誤日志位置Find the error log location if you can't connect to SQL Server

你的 SQL ServerSQL Server 錯誤日志的路徑可能有所不同，具體取決于你的配置設置。The path for your SQL ServerSQL Server error log can vary depending on your configuration settings. 可以在 SQL Server 配置管理器內的啟動參數中找到錯誤日志位置的路徑。The path for the error log location can be found in the startup parameters within the SQL Server Configuration Manager. 請按照以下步驟來找到標識 SQL ServerSQL Server 錯誤日志位置的相關啟動參數。Follow the steps below to locate the relevant startup parameter identifying the location of your SQL ServerSQL Server error log. 你的路徑可能與以下指示的路徑有所不同 。Your path may vary from the path indicated below.

打開“SQL Server 配置管理器”。Open SQL Server Configuration Manager.

展開“服務” 。Expand Services.

右鍵單擊你的 SQL ServerSQL Server 實例，然后選擇“屬性”：Right-click your SQL ServerSQL Server instance, and then select Properties:

選擇“啟動參數”選項卡 。Select the Startup Parameters tab.

在“現有參數”區域中，“-e”后面的路徑是錯誤日志的位置 ：In the Existing Parameters area, the path after "-e" is the location of the error log:

此位置中包含多個錯誤日志文件。There are several error log files in this location. 當前錯誤日志的文件名以 *.log 結尾。The file name that ends with *.log is the current error log file. 以前的日志文件的文件名以數字結尾。File names that end with numbers are previous log files. 每次重新啟動 SQL Server 時都會創建一個新日志。A new log is created every time the SQL server restarts.

在記事本中打開 errorlog.log 文件。Open the errorlog.log file in Notepad.

查找 SQL Server 實例名稱Find SQL Server instance name

在連接到 SQL Server 之前和之后，有幾個選項可用于查找 SQL ServerSQL Server 的名稱。You have a few options for finding the name of your SQL server before and after you connect to SQL ServerSQL Server.

連接到 SQL Server 之前Before you connect to SQL Server

Follow the steps to locate the SQL Server error log on disk. 你的路徑可能與下圖中的路徑有所不同。Your path may vary from the path in the image below.

在記事本中打開 errorlog.log 文件。Open the errorlog.log file in Notepad.

搜索文本“服務器名稱是” 。Search for the text Server name is.

單引號中列出的所有內容都是將連接到的 SQL ServerSQL Server 實例的名稱：Whatever is listed in the single quotes is the name of the SQL ServerSQL Server instance that you'll be connecting to:

名稱的格式為 HOSTNAME\INSTANCENAME。The format of the name is HOSTNAME\INSTANCENAME. 如果只看到了主機名，然后已安裝了默認實例，則實例名稱是 MSSQLSERVER。If you see only the host name, then you've installed the default instance and your instance name is MSSQLSERVER. 連接到默認實例時，只需輸入主機名以連接到 SQL Server。When you connect to a default instance, the host name is all you need to enter to connect to your SQL server.

連接到 SQL Server 時When you're connected to SQL Server

連接到 SQL ServerSQL Server 時，可在三個位置找到服務器名稱：When you're connected to SQL ServerSQL Server, you can find the server name in three locations:

服務器名稱將在“對象資源管理器”中列出：The name of the server is listed in Object Explorer:

服務器名稱將在查詢窗口中列出：The name of the server is listed in the Query window:

服務器名稱將在“屬性”中列出 。The name of the server is listed in Properties.

在“視圖”菜單上，選擇“屬性窗口” ：In the View menu, select Properties Window:

如果連接到別名或可用性組偵聽程序If you're connected to an alias or Availability Group listener

如果連接到別名或可用性組偵聽程序，則將在“對象資源管理器”和“屬性”中顯示該信息。If you're connected to an alias or to an Availability Group listener, that information appears in Object Explorer and Properties. 在這種情況下，SQL ServerSQL Server 名稱可能不是顯而易見的，并且必須進行查詢：In this case, the SQL ServerSQL Server name might not be readily apparent, and must be queried:

連接到 SQL Server。Connect to your SQL server.

打開“新建查詢”窗口。Open a New Query window.

將以下 Transact-SQLTransact-SQL 代碼粘貼到窗口中：Paste the following Transact-SQLTransact-SQL code in the window:

select @@Servername

查看查詢結果，確定連接到的 SQL ServerSQL Server 實例的名稱：View the results of the query to identify the name of the SQL ServerSQL Server instance you're connected to:

后續步驟Next steps

熟悉 SSMS 的最好方式是進行實踐演練。The best way to get acquainted with SSMS is through hands-on practice. 這些教程 和操作說明 文章可幫助你使用 SSMS 的各種功能。These tutorial and how-to articles help you with various features available within SSMS. 這些文章教你如何管理 SSMS 組件，以及如何查找常用功能。These articles teach you how to manage the components of SSMS and how to find the features that you use regularly.

總結

以上是生活随笔為你收集整理的ssms2008 代码自动提示_使用 SSMS 的提示和技巧 - SQL Server Management Studio (SSMS) | Microsoft Docs...的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。