- SSMS Options and Startup
There are a lot of options under Toolbar -> Tools -> Options. You can go over and select the one that makes more sense to you. I make sure the following few things are checked.
- Recover your work
In the middle of writing codes and boom…. SSMS crashed or the machine suddenly rebooted. It is no fun at all. It helps to be aware of ‘AutoRecover’ in SSMS. You can configure how frequently you want to save your work and how long you want to keep the information.
Toolbar -> Tools -> Options -> Environment -> AutoRecover - Open a new query window with SSMS
When you open SSMS, you obviously want to run some queries. You have a few options, but I make sure “Open Object Explorer and query window” is selected.
Toolbar -> Tools -> Options -> Environment -> Startup - Enable line numbers and intelligence
If you are dealing with lots of lines of codes, then you may want to check the ‘Line numbers’ under General. I always Enable Intelligence.
Toolbar -> Tools -> Options -> Text Editor -> Transact-SQl -> (General; Intelligence) - Check your query statistics
I like to turn onSET STATISTICS TIMEandSET STATISTICS IO. This helps me quickly check those statistics on queries I run without explicitly writing on the query all the time.
Toolbar -> Tools -> Options -> Query Execution -> Advanced - Default scripting options
There are at times when you must script multiple objects, check for object existence, permissions, and many more. If you want a consistent scripting experience from SSMS, then check all the scripting options that matter to you from SQL Server Object Explorer.
Toolbar -> Tools -> Options -> SQL Server Object Explorer -> Scripting
- Recover your work
- Drag and Drop
I keep Object Explorer open to browse various database objects. It has a drag and drop feature which I enjoy as I am lazy typing the object’s name in the query editor. - Filter Objects
Imagine a database with hundreds of stored procedures, tables, views, etc. Try to navigate through the object explorer to find the object you are looking for and it crashes on you. You can filter the objects using SSMS.
- Use custom colors to differentiate between environments
When you connect to a server, under Options -> Connection Properties, you can set a custom color for different instances of the database you connect to. Helps it if you are connecting to a different environment from one common machine.
- Speed up your server connections with Registered Servers
Do you connect to a lot of database servers? Are these instances just a replica of one another and you have to deploy the same objects to all of them? Also entering connection information for servers that you connect routinely is a lot boring. Create registered servers and group them appropriately. Life becomes beautiful.
- Use built-in reports
SSMS provides several out-of-the-box standard reports for performance monitoring and troubleshooting purpose. These reports are very helpful for different reasons like knowing the health of a database, troubleshooting some issues, etc. Just right-click any databases or at server in the object explorer and select Reports -> Standard Reports.
- Object Explorer Details
This packs some hidden gems and will certainly make some of your tasks easier. Hit the F7 shortcut or Toolbar -> View -> Object Explorer Details.- Object Properties
Select any object or the node, Object Explorer Detail will give you some good additional information about the selected object/node.
- List all objects and their properties
This probably is my favorite. It helps me without writing any queries learn details about different objects like Tables. Create Date, Row Count, Schema, Space Used are all available. Just click the objects or the node and Object Explorer Details will have everything for you. Make sure to choose what matters to you.
- Search objects across databases
Depending on what node you are on, you can search for objects like tables, stored procedures, etc. within a database or across all the databases on that instance.
- Script/Delete multiple objects
If you want to delete multiple objects with the fewest keystroke then Object Explorer Details is the answer. You can also script those objects.
- Object Properties
