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

此文章提供使用 SQL Server Management Studio (SSMS) 的一些提示和訣竅。This article gives you some tips and tricks for using SQL 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 Server 錯誤記錄檔Access your SQL Server error log
  • 尋找您的 SQL Server 執行個體名稱Find the name of your SQL Server instance

PrerequisitesPrerequisites

若要測試此文章所提供的步驟,您需要 SQL Server Management Studio、SQL 伺服器的存取權,以及 AdventureWorks 資料庫。To test out the steps provided in this article, you need SQL 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.

  1. 開啟 SQL Server Management Studio。Open SQL Server Management Studio.

  2. 連線到 SQL Server。Connect to your SQL server.

  3. 開啟 [新增查詢] 視窗。Open a New Query window.

  4. 將下列 T-SQL 程式碼貼入文字視窗中。Paste the following T-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
    
  5. 反白顯示文字中的 Alter Database 部分,然後選取工具列上的 [註解] 按鈕:Highlight the Alter Database portion of the text, and then select the Comment button on the toolbar:

    [註解] 按鈕

  6. 選取 [執行] 來執行文字中未註解的部分。Select Execute to run the uncommented portion of the text.

  7. 反白顯示除了 Alter Database 命令的所有項目,然後選取 [註解] 按鈕:Highlight everything except for the Alter Database command, and then select the Comment button:

    註解所有項目

    注意

    加上文字註解的鍵盤快速鍵為 CTRL + K、CTRL + CThe keyboard shortcut to comment text is CTRL + K, CTRL + C.

  8. 反白顯示文字中的 Alter Database 部分,然後選取 [取消註解] 按鈕,將它取消註解:Highlight the Alter Database portion of the text, and then select the Uncomment button to uncomment it:

    取消註解文字

    注意

    取消文字註解的鍵盤快速鍵為 CTRL + K、CTRL + UThe keyboard shortcut to uncomment text is CTRL + K, CTRL + U.

  9. 選取 [執行] 來執行文字中未註解的部分。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.

  1. 開啟 [新增查詢] 視窗。Open a New Query window.

  2. 將下列 T-SQL 程式碼貼入文字視窗中:Paste the following T-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
    
  3. 反白文字中的 Alter Database 部分,然後選取工具列上的 [增加縮排] 按鈕,將這段文字向前移動:Highlight the Alter Database portion of the text, and then select the Increase Indent button on the toolbar to move this text forward:

    增加縮排

  4. 再一次反白顯示文字中的 Alter Database 部分,然後選取 [減少縮排] 按鈕,將這段文字向後移動。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:

  1. 連線到 SQL Server。Connect to your SQL server.

  2. 展開 [資料庫] > [AdventureWorks] > [資料表] 。Expand Databases > AdventureWorks > Tables. 資料庫中的所有資料表都會出現。All the tables in the database appear.

  3. 以滑鼠右鍵按一下 [資料表] ,然後選取 [篩選] > [篩選設定] :Right-click Tables, and then select Filter > Filter Settings:

    篩選設定

  4. 在 [篩選設定] 視窗中,您可以修改下列部分篩選設定:In the Filter Settings window, you can modify some of the following filter settings:

    • 依名稱篩選:Filter by name:

      依名稱篩選

    • 依結構描述篩選:Filter by schema:

      依結構描述篩選

  5. 若要清除篩選,以滑鼠右鍵按一下 [資料表] 然後選取 [移除篩選] 。To clear the filter, right-click Tables, and then select Remove Filter.

    移除篩選

存取您的 SQL Server 錯誤記錄檔Access your SQL Server error log

錯誤記錄檔是含有在 SQL Server 執行個體中發生事項之詳細資料的檔案。The error log is a file that contains details about things that occur in your SQL Server instance. 您可以在 SSMS 中瀏覽和查詢錯誤記錄檔。You can browse and query the error log in SSMS. 錯誤記錄檔是位於您磁碟上的 .log 檔案。The error log is a .log file that's located on your disk.

在 SSMS 中開啟錯誤記錄檔Open the error log in SSMS

  1. 連線到 SQL Server。Connect to your SQL server.

  2. 展開 [管理] > [SQL Server 記錄檔] 。Expand Management > SQL Server Logs.

  3. 以滑鼠右鍵按一下 [目前] 錯誤記錄檔,然後選取 [檢視 SQL Server 記錄檔] :Right-click the Current error log, and then select View SQL Server Log:

    在 SSMS 中檢視錯誤記錄檔

在 SSMS 中查詢錯誤記錄檔Query the error log in SSMS

  1. 連線到 SQL Server。Connect to your SQL server.

  2. 開啟 [新增查詢] 視窗。Open a New Query window.

  3. 將下列 T-SQL 程式碼貼入查詢視窗中:Paste the following T-SQL code in your query window:

      sp_readerrorlog 0,1,'Server process ID'
    
  4. 將單引號中之文字修改為您想要搜尋的文字。Modify the text in the single quotes to text you want to search for.

  5. 執行查詢,然後檢視結果:Execute the query, and then review the results:

    查詢錯誤記錄檔

若您已連線到 SQL Server,請尋找錯誤記錄檔的位置Find the error log location if you're connected to SQL Server

  1. 連線到 SQL Server。Connect to your SQL server.

  2. 開啟 [新增查詢] 視窗。Open a New Query window.

  3. 在查詢視窗中貼上以下 T-SQL 程式碼,然後選取 [執行] :Paste the following T-SQL code in your query window, and then select Execute:

       SELECT SERVERPROPERTY('ErrorLogFileName') AS 'Error log file location'  
    
  4. 結果會顯示檔案系統中錯誤記錄檔的位置: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 Server 錯誤記錄路徑可能會不同。The path for your SQL Server errog 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 Server 錯誤記錄檔位置的相關啟動參數。Follow the steps below to locate the relevant startup parameter identifying the location of your SQL Server error log. 您的路徑可能與下面的路徑不同Your path may vary from the path indicated below.

  1. 開啟 [SQL Server 設定管理員]。Open SQL Server Configuration Manager.

  2. 展開 [服務] 。Expand Services.

  3. 以滑鼠右鍵按一下您的 SQL Server 執行個體,然後選取 [屬性] :Right-click your SQL Server instance, and then select Properties:

    設定管理員伺服器屬性

  4. 選取 [啟動參數] 索引標籤。Select the Startup Parameters tab.

  5. 在 [現有參數] 區域中,"-e" 後面的路徑是錯誤記錄檔位置:In the Existing Parameters area, the path after "-e" is the location of the error log:

    錯誤記錄檔

    這個位置中有數個 errorlog.* 檔案。There are several errorlog.* 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.

  6. 在 [記事本] 中開啟 errorlog.log 檔案。Open the errorlog.log file in Notepad.

尋找 SQL Server 執行個體名稱Find SQL Server instance name

您有一些選項可用來在連線到 SQL Server 之前和之後尋找您的 SQL Server 名稱。You have a few options for finding the name of your SQL server before and after you connect to SQL Server.

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

  1. 請遵循下列步驟找出磁碟上的 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.

  2. 在 [記事本] 中開啟 errorlog.log 檔案。Open the errorlog.log file in Notepad.

  3. 搜尋文字「伺服器名稱是」 。Search for the text Server name is.

    在單引號中列出的任何內容,就是您將要連線到的 SQL Server 執行個體名稱:Whatever is listed in the single quotes is the name of the SQL 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 Server 時,您可以在三個位置中找到伺服器名稱:When you're connected to SQL Server, you can find the server name in three locations:

  1. 伺服器名稱將會列在 [物件總管] 中:The name of the server is listed in Object Explorer:

    [物件總管] 中的 SQL Server 執行個體名稱

  2. 伺服器名稱將會列在查詢視窗中:The name of the server is listed in the Query window:

    查詢視窗中的 SQL Server 執行個體名稱

  3. 伺服器名稱將會列在 [屬性] 中。The name of the server is listed in Properties.

    • 在 [檢視] 功能表,選取 [屬性視窗] 。In the View menu, select Properties Window:

      屬性視窗中的 SQL Server 執行個體名稱

如果您已連線到別名或可用性群組接聽程式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 Server 名稱可能不明顯,且必須進行查詢:In this case, the SQL Server name might not be readily apparent, and must be queried:

  1. 連線到 SQL Server。Connect to your SQL server.

  2. 開啟 [新增查詢] 視窗。Open a New Query window.

  3. 將下列 T-SQL 程式碼貼入視窗中:Paste the following T-SQL code in the window:

     select @@Servername
    
  4. 檢視查詢結果以識別您連線的 SQL Server 執行個體名稱:View the results of the query to identify the name of the SQL Server instance you're connected to:

    查詢 SQL 伺服器名稱

後續步驟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.