Tips
Assign shortcuts to commands in SQL Server Management Studio
The SQL Server management tools offer a lot of hidden features, but finding all of these features is sometimes done by accident or by necessity to get a particular job done. One particular feature that would be helpful is to assign shortcuts for particular stored procedures that you run quite frequently. By default SQL Server offers default shortcuts such as Alt+F1 (sp_help), Ctrl+1 (sp_who) and Ctrl+2 (sp_lock). How can you assign your own frequently used stored procedures to shortcut keys?
Auto generate change scripts in SQL Server Management Studio SSMS for tables
As a part of my best practices, I always save the T-SQL scripts used for creation and modification of objects in SQL Server. When creating and modifying tables using SQL Server Management Studio designer it is easy to right click in the designer and select "Generate Change Script...", but is there a way to automatically script the creation and/or modification of tables made through of SQL Server Management Studio (SSMS) designer?
Be Cautious Altering Tables Using SQL Server Management Studio SSMS
Often times we use the SQL Server Management Studio GUI to perform simple tasks such as altering a column in a table. However, you should always check your scripts before blindly performing the actions recommended by SSMS, because they may not always be, and are often not, the best way to perform actions within SQL Server.
Build code using the SQL Server object browsers drag and drop
Trying to remember all of the object names, parameters and columns within tables becomes quite daunting for very large data models. This is where GUI tools such as Enterprise Manager and SQL Server Management Studio provided a big boost to the database market for SQL Server. One feature that is often difficult is writing T-SQL statements for your databases. Trying to remember the exact names of the tables, the columns, the stored procedure parameters is not always that easy. So what can be done to ease this process?
Building and customizing SQL Server script templates
All that SQL Server POWER! All that SQL Server FUNCTIONALITY! There is not enough time in the day to keep it all straight! There is no shame in making use of any short cuts or cheats that allow you to be a more productive and well-versed SQL Professional in order to cope with the sheer volume of expectations you are required to meet on a daily basis. One powerful, but often-overlooked tool at your disposal is the Template Explorer that is a core component of SQL Server Management Studio. In this tip we expose the Template Explorer; making use of the "canned" templates that ship with SSMS, and altering those templates to conform to all those quirks that make our environments unique. I will also show you how to create your own templates
Change Default Value for Select Top n and Edit Top n Rows in SQL Server Studio
While looking through the features in SQL Server 2008 Management Studio (SSMS), I noticed that SSMS only shows the Top 1000 rows when selecting data and the Top 200 rows when editing data for a table. In SQL Server 2005 you used to be able to open the entire table, but this option no longer exists. In this tip we will take a look at how to change the default values for Select Top n Rows and Edit Top n Rows in SQL Server 2008 Management Studio.
Change Setting for Recently Used Files in SQL Server Management Studio SSMS
I noticed in SSMS that there is the ability to show recently used files. In this tip we cover how this value can be set to show more or less files.
Comma Delimited Result Sets in SQL Server 2005 Management Studio
How can I return a comma delimited result set with my queries? I am currently using SSIS to be able to generate a comma delimited result set to a text file, but is just over kill. I thought about using the graphical result set and then massaging the data in Excel or just stringing the results together with a comma, but this just seems like the wrong approach. What is the best way to return a comma delimited result with SQL Server 2005 Management Studio?
Copy column headers and query results in SQL Server Management Studio
One of the nice features with SQL Server is the ability to create result sets from queries into a grid result set. This data can then be copied and pasted in other application such as Excel. The downside to saving the results in a grid is that the column headers don't get copied along with the data. To get around this you could query the data in the text format, so you could copy the results along with the column headers, but then you are faced with formatting issues.
Customize SSMS query window for standard comment block and frequently used commands
As a best practice, most companies follow a standard comment format at the beginning of stored procedures and functions. In addition, it is also a good idea to include comments in all scripts you write even if they are just save as .sql or .txt files. Manually we often add a standard comment block to the header of our scripts using copy and paste, but in this tip we look at how to automate adding a standard comment block for every set of code we write using SSMS.
Customizing the Status Bar for each SQL Server SSMS Connection
Almost every DBA has to manage more than one instance of SQL Server. If you are administering production, test and development servers then there are probably databases with similar names and structures in each of these instances. The scenario becomes more complicated when you are working with multiple logins in each instance at the same time. Chances increase to run a script on an instance other than on which it was intended to run. Although every query pane in SSMS includes information such as server instance and user this information is not always displayed prominently. In this tip, I will show an option in SSMS 2008 to easily identify each instance.
Determine space used for a table using SQL Server Management Studio
Recently I had to restore a copy of a production database to a test server in order for some, well, testing. I had warned the Application Analyst Team for months that we were going to be in a space crunch because while the production database had been growing consistently over time, the test server that they had dedicated for this process was sized based upon an outdated estimate for growth. In short, they had run out their options for using this test instance without first purging unnecessary data and then releasing space from the database back to the file system. The Analyst provided me with the sole table that could be truncated and wanted to know if enough space would be regained to allow the database restore to continue as requested
Different Options for Query Results in SQL Server Management Studio
While looking through the new features and improvements in SQL Server Management Studio (SSMS), we found several options for displaying query results. In this tip we cover what options are available in SSMS and how they can assist you when dealing with query results.
Different SQL Server Management Studio Startup Options
One of the developers asked me if there was a way to open Object Explorer and a New Query Window at the same time whenever he opens SQL Server Management Studio. In this tip we will take a look at different SQL Server Management Studio startup options which can benefit you in your day to day work.
Drag and drop query result columns in SQL Server 2005
There are so many new little features in SQL Server 2005, finding all of them is a challenge and sometimes you just accidentally find things without even looking. One nice feature of the query tool is the ability to display your results in a table format. This makes it very easy to copy and paste the results for SQL Server into an Excel spreadsheet, Access table or some other application. Although this is great there are times when it would be nice to reorder the columns in the query results. For queries that you write this is pretty easy by just changing the column order, but if you have a query that took a long time to run or if you have no control over the output from a stored procedure or some other system function it is sometimes e
Dynamic SQL Server stored procedure execution form in SSMS
The purpose for most stored procedures is for execution within applications, but there are some stored procedures that may be used for administrative purposes and only get executed ad hoc. In addition, during testing you run stored procedures interactively to make sure things are working correctly. You have the ability to run any stored procedure directly from a query window and include the necessary parameters, but is there any easier way to know what parameters a stored procedure requires and to pass the parameters directly to a stored procedure?
Easier way to read your TSQL code in SSMS with word wrap feature
While creating T-SQL scripts with lengthy T-SQL lines you have one of two options; either use the horizontal scroll bar or create line breaks at various points to make the line readable without having to use the scroll bar. Scrolling or having to break the lines of code takes time away from paying attention to coding. It would be nice if there was a word wrap option or something similar. Are there are any options in SQL Server Management Studio to handle word wrap?
Enabling IntelliSense and Refreshing IntelliSense Data in SSMS 2008
There may be times when IntelliSense in SQL Server Management Studio 2008 is not working or does not display the latest schema changes. In this tip we look at a how to check the status and how to update the IntelliSense data.
Error Saving changes is not permitted. in SQL Server 2008 Management Studio
Working in SQL Server Management Studio (SSMS) 2008, I got a warning message while trying to save changes to a table "saving changes is not permitted". After the warning message the changes that I made were rolled back. I have the proper permissions to implement such DDL operations on that table, so how can I control this restriction and what are the pros and cons associated with this permission?
Get script for every action in SQL Server Management Studio
I am always conscious to keep a record of all operations performed on my database servers. Operations through T-SQL in an SSMS query pane can easily be saved in query files. For table modifications through SSMS designer I have predefined setting to generate T-SQL scripts. However there are numerous database and server level tasks that I use the SSMS GUI and I would like to have a script of these changes for later reference. Examples of such actions through the SSMS GUI are backup/restore, changing compatibility level of a database, manipulating permissions, dealing with database or log files or creating/manipulating any login/user. I am looking for any way to generate T-SQL code for such actions, so that it may be kept for later reference.
Hiding System Objects in Object Explorer in SQL Server Management Studio
While looking through the new features and improvements in SQL Server Management Studio (SSMS) we found a potentially interesting one to Hide System Objects in Object Explorer in SQL Server Management Studio. In this tip we will take a look at how to Hide System Objects in Object Explorer.
How to Turn Off SQL Server Management Studio SSMS Auto Recovery Feature
By default the Auto Recovery feature is enabled for SSMS and because of this when opening SSMS it may hang or become unresponsive for some time if the previous session was closed unexpectedly. There is not a way to turn this feature off from within SSMS, but we will look at how this can be done by modifying some registry entries.
Import and Export Registered SQL Servers To Other Machines
In a previous tip we discussed how to register servers in SQL Server Management Studio. Now that I have my registered servers setup the way I like, I need to setup other computers the same way that are used to manage my SQL Servers. Instead of having to manually add the registered servers on each computer in this tip I show you how you can import and export registered servers between computers.
Issues with Run As to Start SQL Server Management Studio with a Different Login
As a best practice in the industry, a DBA often has two logins that are used to access SQL Server; one is their normal Windows login and the other is an admin level login account which has sysAdmin rights on the SQL Server boxes. In addition, most of the time the SQL Server client tools are only installed on the local desktop and not on the servers. So in order to use the different login to connect to SQL Server using SSMS you need to use the "Run as" feature, but when doing this you may get this error message "The directory name is invalid". In this tip we look at how to resolve this issue.
Launching a default Project in SQL Server Management Studio
I have a common set of scripts I rely on whenever I create a new SQL Server 2005 instance. I understand that I can group these scripts and connections into a SQL Server Management Studio Project, but is there an easy method for opening this Project by default when I launch Management Studio?
Manipulating Multiple Objects in SQL Server Management Studio
Every now and then, I see someone doing repetitive tasks in SQL Server Management Studio such as dropping a stored procedure one by one when they need to remove ten or scripting out a single object at a time because they can't select multiple objects from the Object Explorer. I have even seen people create elaborate scripts to perform tasks that involved manipulating multiple database objects to avoid these mundane tasks. In this tip I will show you how this can be done simply by using SQL Server Management Studio.
Maximizing work space with SSMS by using separate results tabs
In a previous tip, I talked about how to use the word wrap feature in SSMS query editor for enhanced readability of your T-SQL code when using SQL Server Management Studio. Although this is helpful for reading your code there is often the need to analyze your code, the data or designers inside SSMS and being able to see as much as possible. In this tip I will show you other ways to customize options in SSMS that are specific for improved view of code, data, designers and other related panes like execution plans and messages.
Options for scripting SQL Server database objects
SQL Server Management Studio is rich with features. So rich, in fact, that after almost 3 years I am still finding new tools in the application that afford me more control over my databases and the methods for supporting them. Recently I found myself needing to script out a database and its objects for reviewing a problem offline. I was going to be traveling without my laptop (ooh, that phrase still makes me break out in a cold sweat) and I needed to be able to look over how the tables in a specific database were structured. I scripted out the tables and was not satisfied with the format and spent quite some time reformatting the scripts that were generated by Management Studio. After the fact I started to wonder if perhaps there was a
Property Owner is not available for Database SSMS error
When you try to launch the database mirroring GUI or some other database property window in SSMS you get this error: Cannot show requested dialog.(SqlMgmt) Property Owner is not available for Database'[XXXX]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo). In this tip we show how to resolve this issue.
Registering SQL Server instances for easier management
I have to manage a number of SQL Server instances across the network. To access the servers I generally use IP addresses along with a custom port number if one is configured. I could use the server name, but the server names are getting longer and also less meaningful. It is a bit time consuming and annoying to keep track of each server by its IP, so it would be nice if there was an easier way. It would also be nice to keep associated properties of the connection like default database, default protocol etc. also persistent in certain cases. Although saving connection properties in the connection frame of SSMS may serve this purpose, but it also contains ad-hoc created connections and I often purge this information. Is there a way to arra
Scripting out TSQL commands generated by SQL Server Management Studio
With SQL Server Management Studio you have the ability to do just about everything you can do using T-SQL commands. One problem with using the GUI is that it is difficult to remember everything you clicked on to reproduce the behavior a second time. Another issue is that most of what you do through the GUI is done immediately which may not always be the best scenario. How can I capture what SQL Server is doing so I can reproduce the behavior or run the commands at a later time.
SQL Management Studio Snapshot Add in
I attended a talk the other day and the speaker was showing SQL Server Potentials and Professionals alike on how easy it was to manage SQL Server from Management Studio.
SQL Query Analyzer Shortcuts
Using Query Analyzer is a great tool for testing queries, running ad hoc queries, troubleshooting queries etc... There is a lot of functionality as well as a lot of shortcuts built into the tool, but finding the shortcuts or remembering them is sometimes not that easy.
SQL Server 2008 R2 Generate Scripts Wizard with Database Schema and Data
While looking through the new features and improvements in SQL Server 2008 R2, we found a potentially interesting addition within the Generate Scripts Wizard in SQL Server Management Studio. Using the Generate and Publish Scripts wizard you can script out data stored in the tables, which was not allowed in the earlier versions of SQL Server.
SQL Server Management Studio customized startup options
SQL Server Management Studio (SSMS) is now the primary tool that we all use to manage SQL Server. Whenever I open up SSMS I always go through the same steps to connect to a server and open certain query files. Are there any shortcuts or alternative ways of starting SSMS?
SQL Server Management Studio keyboard shortcuts (Part 1 of 2)
As responsibilities are growing every day, a DBA or developer needs to improve his/her productivity. One way to do this is to use as many shortcuts as possible instead of using your mouse and the menus. In this tip we take a look at common tasks you may perform when using SSMS and the associated shortcut keys.
SQL Server Management Studio SQLCMD mode option
I recall in SQL Server 2005 Management Studio (SSMS) that I could quickly go into SQLCMD mode using a button on the SQL Editor toolbar. What happened to it in SSMS after SQL Server 2005, I can't find it? In this tip we walk through how to re-enable this feature.
SSMS keyboard shortcuts (Part 2 of 2)
As DBA and Developer responsibilities grow on a daily basis, we how can we improve our productivity? Due to the time it takes to use the toolbar, menu bar or mouse, do I have any other options? Are there keyboard shortcut for SQL Server Management Studio? Could these help me improve my productivity? Check out this tip to learn more.
SSMS Timeout Expired Error When Making Changes To a Table
I was required to add a new integer type column with an identity property to an existing table through SSMS designer. The operation failed and generated a timeout message. The table was large and I expected it would take some time for this task, but I didn't think it would have a timeout issue. In this tip we look at how to set this timeout value as well as look at other timeout values that can be set for SQL Server.
Steps to clear the server list in SQL Server Management Studio
Generally we connect to several SQL Server instances. Once connected successfully, this server history is shown in the server connection drop down list. In some cases we might have similar names for servers as such as using P for production, D for Development, U for UAT and T for Test environments. Because of this there may be a chance that we connect to the wrong server. Also, there may be times when we connect to servers infrequently or a server may go away, but the name is still in the list. For these types of scenarios we want to clear the server list and only show servers we really need to work with, but unfortunately there is not an option within SSMS.
Troubleshooting IntelliSense in SQL Server Management Studio 2012
IntelliSense can be a very powerful ally for the T-SQL developer. It can significantly reduce keystrokes, prevent you from guessing at object names, make sure you spell them correctly (and use the right case), and can help you understand the interface to several programmable entities such as stored procedures and built-in functions. In Management Studio 2012, there are some enhancements to IntelliSense that will make it even more powerful - including more intelligent caching, and partial keyword matching (so you no longer have to remember what letter the waits DMV *starts* with - just type "waits" and it will narrow it down).
I have seen several cases in forums, newsgroups and on twitter where folks complain that IntelliSense is "not work
Using Object Explorer Details and Object Search Feature of SSMS 2008
While looking through the new features and improvements in SQL Server 2008 Management Studio (SSMS) we found a potentially interesting one called Object Explorer Details. Database Developers and Administrators can use Object Explorer Details to get a lot of useful information about the object which they have selected in Object Explorer. Can you give us a detailed explanation of how we go about using Object Explorer Details?
Using Solutions and Projects to manage your SQL Server code
When developing code for a project there is often more than one component that you need to work with for the entire project. The project may include new tables, table changes, new stored procedures, changes to stored procedures, etc... Keeping all of these components straight as well as logically grouped together is sometimes a challenge in itself. Are there are any built-in tools that allow you better manage project components vs. one big file?
Using the SSMS Query Editor in SQLCMD Mode
The SQL Server Management Studio Query Editor allows editing and running SQLCMD scripts. Although this feature is not very popular, it comes handy when you need to run and/or schedule scripts against multiple servers or databases, or when you need to test scripts that you plan to run using the command line, especially if you manage a small number of servers. This tip will offer you the basics of editing SQLCMD scripts using the Query Editor.
Top 10
SSMS Timeout Expired Error When Making Changes To a Table
I was required to add a new integer type column with an identity property to an existing table through SSMS designer. The operation failed and generated a timeout message. The table was large and I expected it would take some time for this task, but I didn't think it would have a timeout issue. In this tip we look at how to set this timeout value as well as look at other timeout values that can be set for SQL Server.
SQL Server Management Studio keyboard shortcuts (Part 1 of 2)
As responsibilities are growing every day, a DBA or developer needs to improve his/her productivity. One way to do this is to use as many shortcuts as possible instead of using your mouse and the menus. In this tip we take a look at common tasks you may perform when using SSMS and the associated shortcut keys.
Troubleshooting IntelliSense in SQL Server Management Studio 2012
IntelliSense can be a very powerful ally for the T-SQL developer. It can significantly reduce keystrokes, prevent you from guessing at object names, make sure you spell them correctly (and use the right case), and can help you understand the interface to several programmable entities such as stored procedures and built-in functions. In Management Studio 2012, there are some enhancements to IntelliSense that will make it even more powerful - including more intelligent caching, and partial keyword matching (so you no longer have to remember what letter the waits DMV *starts* with - just type "waits" and it will narrow it down).
I have seen several cases in forums, newsgroups and on twitter where folks complain that IntelliSense is "not work
SSMS keyboard shortcuts (Part 2 of 2)
As DBA and Developer responsibilities grow on a daily basis, we how can we improve our productivity? Due to the time it takes to use the toolbar, menu bar or mouse, do I have any other options? Are there keyboard shortcut for SQL Server Management Studio? Could these help me improve my productivity? Check out this tip to learn more.
SQL Server Management Studio customized startup options
SQL Server Management Studio (SSMS) is now the primary tool that we all use to manage SQL Server. Whenever I open up SSMS I always go through the same steps to connect to a server and open certain query files. Are there any shortcuts or alternative ways of starting SSMS?
Different Options for Query Results in SQL Server Management Studio
While looking through the new features and improvements in SQL Server Management Studio (SSMS), we found several options for displaying query results. In this tip we cover what options are available in SSMS and how they can assist you when dealing with query results.
SQL Server Management Studio SQLCMD mode option
I recall in SQL Server 2005 Management Studio (SSMS) that I could quickly go into SQLCMD mode using a button on the SQL Editor toolbar. What happened to it in SSMS after SQL Server 2005, I can't find it? In this tip we walk through how to re-enable this feature.
Get script for every action in SQL Server Management Studio
I am always conscious to keep a record of all operations performed on my database servers. Operations through T-SQL in an SSMS query pane can easily be saved in query files. For table modifications through SSMS designer I have predefined setting to generate T-SQL scripts. However there are numerous database and server level tasks that I use the SSMS GUI and I would like to have a script of these changes for later reference. Examples of such actions through the SSMS GUI are backup/restore, changing compatibility level of a database, manipulating permissions, dealing with database or log files or creating/manipulating any login/user. I am looking for any way to generate T-SQL code for such actions, so that it may be kept for later reference.
Hiding System Objects in Object Explorer in SQL Server Management Studio
While looking through the new features and improvements in SQL Server Management Studio (SSMS) we found a potentially interesting one to Hide System Objects in Object Explorer in SQL Server Management Studio. In this tip we will take a look at how to Hide System Objects in Object Explorer.
Using the SSMS Query Editor in SQLCMD Mode
The SQL Server Management Studio Query Editor allows editing and running SQLCMD scripts. Although this feature is not very popular, it comes handy when you need to run and/or schedule scripts against multiple servers or databases, or when you need to test scripts that you plan to run using the command line, especially if you manage a small number of servers. This tip will offer you the basics of editing SQLCMD scripts using the Query Editor.
Last 10
SSMS Timeout Expired Error When Making Changes To a Table
I was required to add a new integer type column with an identity property to an existing table through SSMS designer. The operation failed and generated a timeout message. The table was large and I expected it would take some time for this task, but I didn't think it would have a timeout issue. In this tip we look at how to set this timeout value as well as look at other timeout values that can be set for SQL Server.
Troubleshooting IntelliSense in SQL Server Management Studio 2012
IntelliSense can be a very powerful ally for the T-SQL developer. It can significantly reduce keystrokes, prevent you from guessing at object names, make sure you spell them correctly (and use the right case), and can help you understand the interface to several programmable entities such as stored procedures and built-in functions. In Management Studio 2012, there are some enhancements to IntelliSense that will make it even more powerful - including more intelligent caching, and partial keyword matching (so you no longer have to remember what letter the waits DMV *starts* with - just type "waits" and it will narrow it down).
I have seen several cases in forums, newsgroups and on twitter where folks complain that IntelliSense is "not work
SQL Server 2008 R2 Generate Scripts Wizard with Database Schema and Data
While looking through the new features and improvements in SQL Server 2008 R2, we found a potentially interesting addition within the Generate Scripts Wizard in SQL Server Management Studio. Using the Generate and Publish Scripts wizard you can script out data stored in the tables, which was not allowed in the earlier versions of SQL Server.
Property Owner is not available for Database SSMS error
When you try to launch the database mirroring GUI or some other database property window in SSMS you get this error: Cannot show requested dialog.(SqlMgmt) Property Owner is not available for Database'[XXXX]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo). In this tip we show how to resolve this issue.
SSMS keyboard shortcuts (Part 2 of 2)
As DBA and Developer responsibilities grow on a daily basis, we how can we improve our productivity? Due to the time it takes to use the toolbar, menu bar or mouse, do I have any other options? Are there keyboard shortcut for SQL Server Management Studio? Could these help me improve my productivity? Check out this tip to learn more.
SQL Server Management Studio SQLCMD mode option
I recall in SQL Server 2005 Management Studio (SSMS) that I could quickly go into SQLCMD mode using a button on the SQL Editor toolbar. What happened to it in SSMS after SQL Server 2005, I can't find it? In this tip we walk through how to re-enable this feature.
SQL Server Management Studio keyboard shortcuts (Part 1 of 2)
As responsibilities are growing every day, a DBA or developer needs to improve his/her productivity. One way to do this is to use as many shortcuts as possible instead of using your mouse and the menus. In this tip we take a look at common tasks you may perform when using SSMS and the associated shortcut keys.
Customize SSMS query window for standard comment block and frequently used commands
As a best practice, most companies follow a standard comment format at the beginning of stored procedures and functions. In addition, it is also a good idea to include comments in all scripts you write even if they are just save as .sql or .txt files. Manually we often add a standard comment block to the header of our scripts using copy and paste, but in this tip we look at how to automate adding a standard comment block for every set of code we write using SSMS.
How to Turn Off SQL Server Management Studio SSMS Auto Recovery Feature
By default the Auto Recovery feature is enabled for SSMS and because of this when opening SSMS it may hang or become unresponsive for some time if the previous session was closed unexpectedly. There is not a way to turn this feature off from within SSMS, but we will look at how this can be done by modifying some registry entries.
Change Setting for Recently Used Files in SQL Server Management Studio SSMS
I noticed in SSMS that there is the ability to show recently used files. In this tip we cover how this value can be set to show more or less files.
