Tips and tricks for using SQL Server Management Studio (SSMS)

This article includes some tips and tricks for using SQL Server Management Studio (SSMS). This article shows you how to:

• Comment/uncomment your Transact-SQL (T-SQL) text
• Indent your text
• Filter objects in Object Explorer
• Access your SQL Server error log
• Find the name of your SQL Server instance

Prerequisites

To test the steps provided in this article, you need SQL Server Management Studio, access to an instance of SQL Server and an AdventureWorks database.

• Install SQL Server Management Studio.
• Install SQL Server Developer Edition.
• Download an AdventureWorks sample database. To learn how to restore a database in SSMS, see Restoring a database.

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 isn't executed.

1. Open SQL Server Management Studio.

2. Connect to your SQL Server instance.

3. Open a New Query window.

4. Paste the following Transact-SQL code in your text window.

   SQL

   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. 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. Highlight everything except for the Alter Database command, and then select the Comment button:

   

   Note

   The keyboard shortcut to comment text is CTRL + K, CTRL + C.

8. Highlight the Alter Database portion of the text, and then select the Uncomment button to uncomment it:

   

   Note

   The 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. Paste the following Transact-SQL code in your text window:

   SQL

   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. Highlight the Alter Database portion of the text, and then select the Increase Indent button on the toolbar to move the highlighted text forward:

   

4. Highlight the Alter Database portion of the text again, and then select the Decrease Indent button to move the highlighted 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 nodes in Object Explorer:

1. Connect to your SQL Server instance.

2. 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:

5. • Filter by name:

     

   • Filter by schema:

     

6. To clear the filter, right-click Tables, and then select Remove Filter.

   

Access your SQL Server error log

The error log is a file that contains details about things that occur in your SQL Server instance. You can browse and query the error login SSMS. The error log is a .log file that exists in your file system.

Open the error log in SSMS

1. Connect to your SQL Server instance.

2. Expand Management > SQL Server Logs.

3. Right-click the Current error log, and then select View SQL Server Log:

   

Query the error log in SSMS

1. Connect to your SQL Server instance.

2. Open a New Query window.

3. Paste the following Transact-SQL code in your query window:

   SQL

   EXECUTE 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:

   

Find the error log location if you're connected to SQL Server

1. Connect to your SQL Server instance.

2. Open a New Query window.

3. Paste the following Transact-SQL code in your query window, and then select Execute:

   SQL

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

4. The results show the location of the error log in the file system:

   

Find the error log location if you can't connect to SQL Server

The path for your SQL Server error log can vary depending on your configuration settings. The path for the error log location can be found in the SQL Server startup parameters within the SQL Server Configuration Manager.

Follow these steps to locate the relevant startup parameter that identifies the location of your SQL Server error log. Your path might vary from the path indicated in the example.

1. Open SQL Server Configuration Manager.

2. Expand Services.

3. Right-click your SQL Server instance, and then select Properties:

   

4. Select the Startup Parameters tab.

5. 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. The file name that ends with *log is the current error log file. File names that end with numbers are previous log files. A new log is created every time the SQL Server restarts.

6. Open the errorlog.log file in your preferred text editor.

Find SQL Server instance name

You have a few options to find the name of your SQL Server instance before and after you connect to SQL Server.

Before you connect to SQL Server

1. Follow the steps to locate the SQL Server error log on disk.

2. Open the errorlog.log file in Notepad.

3. Search for the text Server name is.

   The text listed in the single quotes is the name of the SQL Server instance to use in the connection:

   

   The format of the name is HOSTNAME\INSTANCENAME. If you see only the host name, then you've installed the default instance and your instance name is MSSQLSERVER. When you connect to a default instance, the host name is all you need to enter to connect to your SQL Server. Your path might vary from the path in the sample image.

When you're connected to SQL Server

When you're connected to SQL 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. In this case, the SQL Server instance name might not be readily apparent, and must be queried:

1. Connect to your SQL Server instance.

2. Open a New Query window.

3. Paste the following Transact-SQL code in the window:

   SQL

   SELECT @@Servername;
   

4. View the results of the query to identify the name of the SQL Server instance you're connected to:

   

Related content

• Quickstart: Connect and query a SQL Server instance using SQL Server Management Studio (SSMS)
• Script objects in SQL Server Management Studio
• Use templates in SQL Server Management Studio
• SQL Server Management Studio components and configuration