How to Filter Objects in SQL Server Management Studio (SSMS) - SQL Server / TSQL Tutorial Part 19

Scenario:

I am tired of scrolling through all the objects such as tables, Stored Procedure and View to find an object. Sometime I need to script Stored Procedure to see the definition and it take me couple of minutes just to find the required Stored Procedures as we have 100,s of Stored Procedure. Is there a way I can filter objects in SSMS?


Solution:

SQL Server Management Studio(SSMS) let us filter objects such as tables. Stored Procedure and Views. It really helps to get to object quickly in case when you have a lot of objects in your single database. 


Here is how you can filter the objects. Go to Database and then to Tables, Views or Stored Procedures under Programmability and then Right click.

How to filter objects in SQL Server Management Studio- SQL Server Tutorial



In next windows, we will be able to provide filter criteria. We can filter when equal, contains or not equal as you can see below.

How to filter objects( tables, views and Stored Procedures) in SSMS - SQL Server Tutorial



Once filter is applied, you will see only object which qualify filter criteria.

How to find object quickly in SSMS- SQL Server Tutorial Step by Step



Once you are done and would like to see all the object again or change filter criteria, Right Click on Tables, Views or Stored Procedures and then go to Filter and then hit Remote Filter or Filter Setting to change current criteria.


How to remove Filter setting in SSMS For objects - SQL Server Tutorial for Beginners


Edit Table Rows / Records in SQL Server Management Studio ( SSMS) - SQL Server Tutorial / TSQL Tutorial Part 18

Scenario:

I know that we can write update statements to update records in a table in SQL Server Management Studio(SSMS), Is there a way to update record visually?

Solution:

Yes, SQL Server Management Studio(SSMS) let you update records by using Visual Editor. To update value in a table , follow below steps.

Step 1: 
Right click on table and then go to Edit Top 200 rows as shown below.
How to visual Edit Rows in SSMS - SQL Server Tutorial


Step 2:
Go ahead and make changes to the records as you like. I changed first name from M to Mohammed. Once done, you can close the windows and changes will be saved. If you would like to insert new record, go to last line and type all the required values.
Visual Edit or Insert Records/Rows in SSMS - SQL Server Tutorial Step by Step



How to Generate DDL Scripts(Create) from SQL Server Management Studio (SSMS) - SQL Server / TSQL Tutorial Part 17

Scenario:

We often need to get DDL( Data Definition Language) scripts for different objects such as tables, views and Stored Procedures etc.
Is it possible to generate Create Table statement for a table in SSMS?
If I need to generate Create statement for all the tables/view, Will SSMS let me do it?


Solution:

Generate Script for Single Object:

SQL Server Management Studio ( SSMS) does provide the feature to script single or all objects. To generate DDL script for single object, You can right click on the object and then choose the statement you like to create. Let's say if we would like to generate create table statement for existing table, we will choose below options.



How to generate DDL script ( Create Table) for an object in SSMS - SQL Server Tutorial



How to Generate DDL Scripts for More than One Object:
This is also easy. You need to Right click on the Database in which the objects exists. Then go to Tasks and hit Generate Scripts.

How to generate scripts for multiple objects in SSMS - SQL Server Tutorial


In Next window, you will be able to choose the objects for which you would like to create script. 

Generate Scripts for all the objects in a Database by using SQL Server Management Studio ( SSMS)



The next window will ask you if you like to have the scripts open in new Query Window or you would like to save in a file. Sometime you have a lot of objects and those script might not fit in Query Window, you can always save to file and then use it when required.

How to script all tables/Views/Stored Procedures from a database in SQL Server


Once you hit Next, You will see the progress windows as shown below. Once done. You can hit Finish. 
SQL Server Management Studio- How to script all objects in SSMS


How to use Templates in SQL Server Management Studio (SSMS) - SQL Server / TSQL Tutorial Part 16

Scenario:

I am new developer or been working from long time but my memory is not that great;) every time I need to create an object, I forget the script and I have to go to Google or Microsoft website to get the  template for create, alter object. Does SSMS has something like that so I can use that?


Solution:

SQL Server Management Studio does have built-in templates and you can use them whenever you need them.

Here are the steps how to use Built-in Templates in SSMS.

Step 1:

Go to View Drop down and then choose Template Explorer
How to use Templates in SSMS - SQL Server Tutorial

Template Explorer will open and you will see a lot of templates as you can see some of them in below snapshot.
How to use built-in Templates in SSMS - SQL Server Tutorial Step by Step



Step 2:
Double click on template you would like to use. I clicked on Create Table and below window opened.
How to use Templates in SQL Server Management Studio- SQL Server Training


Step 3:

Now we can replace the existing values in template and change as we like. To provide the values for Template Parameters, Go to Query and then hit Specify Values for Template Parameter as shown below.

How to use Template in SSMS to create Table - SQL Server Tutorial for Beginners

Step 4:

Final step is to provide the values. Once you click Specify Values for Template Parameter, Below windows will open. Go ahead and make changes as per your requirement.

How to use built-in Templates in SSMS - SQL Server/TSQL Tutorial


After providing different parameter values, once you hit Ok. Your query will be ready. I only made change for Database name and Table Name, here is what I get after I hit Ok.


How to Use Templates in SQL Server Management Studio - SQL Server Tutorial

How to Display Query and Results in Separate Tab in SQL Server Management Studio (SSMS) - SQL Server / TSQL Tutorial Part 15

Scenario:

Every time I run query in SQL Server Management Studio (SSMS), the results are shown under the query window. To see the full result window, I have to drag up. Can I see the Query and Results in separate Tabs in SSMS?

Solution:

To see the Result Windows in separate tab, you will be going to Options as shown below.
How to see results in separate tab in SSMS - SQL Server Tutorial

Go to Query Results, Then SQL Server and then Results to Grid and check the Check Box
Display results in separate Tab
and Switch to results tab after the query executes
How to see results in separate tab in SQL Server Management Studio - SQL Server Tutorial



Now If you open a new query window and execute TSQL query, you will see the results window as separate tab as shown below.

How to see results in separate Tab in SSMS - SQL Server Tutorial Step by Step


I noticed one thing, If you right click on table and choose Select top 100 rows , you will see the query and results as you normal do in SSMS. It will not show the results in separate Tab. To do that you have to open new query and paste your query and execute.


How to View Multiple Queries and Results Side by Side in SQL Server Management Studio (SSMS) - SQL Server / TSQL Tutorial Part 14

Scenario:

You are working on multiple queries and seeing results. You want to see the queries and result windows at the same time in SQL Server Management Studio.How can you do that?

Solution:

This is really helpful feature of SQL Server Management Studio(SSMS), specially while debugging our scripts and comparing results. By default in SSMS, When you click on Query Window, only results related to that query window will show.

To see the Multiple queries and result windows, Go to Windows Drop down and choose Horizontal Tab Group or Vertical Tab Group.

We have selected Horizontal Tab Group, here is what we will see for our two queries and results after this setting.
How to see multiple Queries and Result Windows in SSMS - SQL Server Tutorial


How to see multiple queries and results in SSMS - SQL Server Tutorial




To see the queries and results in vertical , you need to go to Windows and select New Vertical Tab Group. Once you do that you will see the result as shown below.

How to see multiple queries and results in vertical position in SSMS - SQL Server Tutorial



To remove or to get to previous position. Go to Windows and then choose Go to Previous Position.
How to see multiple queries windows and result windows in SSMS - SQL Server Tutorial


How to Split Query Window in SQL Server Management Studio (SSMS) - SQL Server / TSQL Tutorial Part 13

Scenario:

You have written long TSQL script in SSMS. You are in process of debugging and want to take a look on start and end of code at the same time. You don't have two monitor available. Is it possible if I can split window to see the same code and take a look in different parts of code at same time?

Solution:

SQL Server Management Studio (SSMS) does provide the facility to split Query window so you can take a look at different parts of code.
You will see a small button at Right Upper Corner in your query window as shown below.

How to split single windows in SSMS for debugging - SQL Server Tutorial 



Press the button and drag it down and you will see the single window will split into two windows and now you can scroll in them independently.

How to Split Single Window in SSMS - SQL Server Tutorial



Now you can take a look into different parts of same code at the same time. To remote the split. 
Go to Windows and Click Remove Split.
Split Query window into two Windows for Debugging in SSMS - SQL Server Tutorial


How to Change Color and Fonts in SQL Server Management Studio ( SSMS) - SQL Server / TSQL Tutorial Part 12

Scenario:

I am SQL Server developer and I don't like the default fonts used in SQL Server Management Studio, they are too small and I don't like the style. Also I would like to change the color of text. How Can I change text properties as per my choice in SSMS?


Solution:

SQL Server Management Studio (SSMS) let you customize text properties as you like. Open SSMS, Go to Tools and then Options as shown below.

How to change Font Size,Font Color and Font Style in SSMS - SQL Server Tutorial

Go to Environment and then click on Fonts and Colors. 
Under options you can make changes as you like.

How to Change Font Size in SSMS,Font Color in SSMS,Font Style in SSMS - SQL Server Tutorial


How to add Line Numbers in SQL Server Management Studio ( SSMS) - SQL Server / TSQL Tutorial Part 11

Scenario:

As sql server developer we write a lot of scripts and every time error occur, it say error happened on line number x.

if you are using 3 lines code like me, you might be Ok to see which is third line :) but if your have 100,s of lines of code, it is good idea if you can see the line numbers next to code.  Question is, How to enable line Numbers and if I will copy this script, will line numbers will be copied as well?

Solution:

Open SQL Server Management Studio(SSMS). Go to Tools and then Options as shown below.
How to add line numbers in SSMS - SQL Server Tutorial

Now go to Text Editor, Then Transact-SQL and hit General. On Option windows check the Line Number Box under Display as shown below.

How to Add Line Numbers to SQL Script in SSMS - SQL Server Tutorial


Now you should see the Line Numbers next to your script, and If you will copy the script , it will not copy the line numbers with it. Line Numbers are there only for display.

Add line numbers to sql script in SSMS - SQL Server / T SQL Tutorial





How to create Custom Shortcuts in SQL Server Management Studio ( SSMS) - SQL Server / TSQL tutorial Part 10

Scenario:

You can create shortcut for most commonly used Stored Procedures so you don't have to write every time. You can simple use Shortcut keys to run them.


Solution:

Open SQL Server Management Studio (SSMS). To to Tools and then go to options as shown below.
How to create shortcuts in SSMS For mostly used Stored Procedures - SQL Server Tutorial


Go ahead and add the Stored Procedures in front of Shortcuts. I have added sp_order user Stored Procedure in front of Ctrl+3. 


How to create Shortcuts for Stored Procedures in SSMS - SQL Server Tutorial


Once you open the New Query and then Press Ctrl+1, it will run sp_who Stored Procedure. You don't have to worry about scope of database as it will run in any database. 

To use the Shortcut for sp_Order , you need to change the Query scope to Database in which sp_Order exists. If you will run in any other database, it will through an error as Stored Procedure will not be present.



Select and Edit Vertical Blocks in SQL Server Management Studio ( SSMS) - SQL Server / TSQL Tutorial Part 9

Scenario:

We often come to situation where we need to make same changes to multiple line of code. This is great feature to make quick change to tsql script.

Take a look into below script,I would like to change Testdb1 to testdb2 for first three queries. We might think that we can use Find and Replace, that can be used but we have to replace one item at a time as our each query is using Testdb1. By using SSMS Edit Vertical Blocks feature we can replace Testdb1 with testdb2 for three queries quickly.

Use master
go
Select * From testdb1.dbo.test1 union all
Select * From testdb1.dbo.test2 union all
Select * From testdb1.dbo.test3 union all
Select * From testdb1.dbo.test4 union all
Select * From testdb1.dbo.test5 union all
Select * From testdb1.dbo.test


Solution:

 It is simple to use , How down the Alt key and then select area you would like to select.

How to Select and Edit Vertical Blocks in SSMS - SQL Server Tutorial


Once the area is selected, Type the new word you would like to use, in our case we want to replace with TestDB2.
How to Select and Replace vertically in SSMS - SQL Server Tutorial


I noticed one thing, If there will be space( line) between the queries, It will type the new word there as well. that is annoying so make sure your queries don't have space between them.

Trying to add word to blank lines as part of replace in SSMS vertical Select and Replace Feature





Cycle Through Clipboard Ring in SQL Server Management Studio (SSMS) - SQL Server / TSQL Tutorial Part 8

Scenario:

We often copy and past queries to work with them in different Query Windows. Let's say you have close all the Query Windows and would like to go to one of the query you had copied. You can use this feature to go back to 20 items you had copied to clipboard.

Solution:

This is great feature of SSMS, we can cycle through last 20 copied items.
Important Note: If you would have closed the SSMS and reopened, you will not be able to cycle through Clipboard Ring.

Step 1: 
Open a New Query Windows in SSMS. Go to Edit Drop Down and then choose Cycle Clipboard Ring. This will paste copied query to new query window. You can keep repeating to go to previous clipboard copied items.


How to loop through Clipboard Ring in SSMS to recover copied queries- SQL Server Tutorial


Instead of going to Edit and then Cycle Clipboard Ring every time, we can use Ctrl+Shift+V shortcut to paste copied items from clipboard.




Configure StartUp Environment in SQL Server Management Studio (SSMS) - SQL Server / TSQL Tutorial Part 7

Scenario:

If you open SQL Server Management Studio, it will ask you provide the SQL Server Instance Name. Once you do that, It opens Object Explorer. You would like to open Object Explorer and New Query window on startup.

Solution:

SQL Server Management Studio(SSMS) provide us different options for startup. Let's make some changes for Startup options.

Step 1: 
Open SQL Server Management Studio and go to Tools and then Options as shown below.
How to change Startup options in SQL Server Management Studio- SQL Server Tutorial


Step 2:
Under the Keyboard, go to Startup and then make changes as shown below.
You have following options
Open Object Explorer
Open New Query Window
Open Object Explorer and Query Window
Open Object Explorer and Activity Monitor
Open Empty Environment

You can choose according to what works best for you.

How to change Startup Options in SSMS - SQL Server Tutorial Step by Step


You have to close the SSMS and reopen to implement changes you just made. Next time when you will open your SSMS, it will open Object Explorer and Query window for you on startup.

How to Set Status Bar Color in SSMS for Different SQL Server Instances - SQL Server / TSQL Tutorial Part 6

Scenario:

Let's say you are working as DBA or Developer and you can connect to different development servers, UAT Servers and Productions Servers and have permission to execute scripts. You always check the server name in SSMS before you run the script. Mistakes can happen. SSMS provide us an extra feature that we can use to color the Status Bar to make sure we are in correct environment.

Solution:

Let's say we want to color status bar to green for our development servers, yellow for SIT/QA, Orange for UAT and Red for Production.

Step 1:
Let's say we are configuring SSMS for our development server. Open SQL Server Management Studio (SSMS) and provide SQL Server Instance and then click on options as shown below.

How to change Status Bar Color in SQL Server Management Studio- SQL Server Tutorial


Step 2:
Go to User Custom Color, then hit Select button and choose the color you like. In our case we are choosing green color and then hit connect.
How to change Status color in SQL Server Management Studio- SQL Server Tutorial

Step 3:
Now once you open New Query, you will see Status Bar color will be green. You can close the SSM and reopen, setting is saved and every time you will connect to this server, status bar will be green.

Perform the above Steps for Other servers you like to connect.




How to Connect to Several SQL Servers in One Click( Registered Servers Group) - SQL Server / TSQL Tutorial Part 5

Scenario:

Think about a situation where you need to get all Database Names for Development servers or Check if Login exists on all UAT Servers. In these situations you have to connect to each SQL Server Instance individually and run the query.You are curious if there is a way when you can run same query on multiple Servers with one click?


Solution:

We can create Registered Servers Groups and then add the SQL Server Instances to it. Let's take a look how we can create Registered Groups.

Step 1: 
Open SSMS and then go to View and choose Registered Servers.
How to create Registered Servers Group



Registered Servers windows will open as shown below.
How to add SQL Server Instance to Registered Servers Group in SQL Server




Step 2: Add New Group to Registered Servers Group
Right click on Local Server Group and then go to New Server Group. Let's say we want to add our all Development Servers to this group.

How to create Server Groups in SQL Server - SQL Server Tutorial

Provide the Group Name, Description and hit Ok.


Step 3: Add SQL Server Instance to Server Group
As our Development-Server Group is created, Now we can add SQL Server Instance to it.

How to add SQL Server Instance to Registered Server Group in SQL Server


Now we have to provide SQL Server Instance information that we are going to add to Registered Group ( Development-Servers)

Provide the SQL Server Instance Name, Test and Finally hit Save button.

How to add SQL Server Instance to Registered Servers Group in SQL Server.

Now repeat the step# 3 to add more SQL Server Instance to the Registered Server Group. 

Step 4: How to Run Query on Registered Servers Group
Click on the Registered Servers Group Name and then hit New Query. Any query you will run in newly open window, will run on all the SQL Server Instances which are included in Registered Servers Group.


How to run SQL Server Query on Multiple SQL Server Instances by using Registered Servers.